CREATE OR REPLACE PACKAGE ORAVC_ENGINE AUTHID CURRENT_USER AS

/*
<main_description>
  creator: Andrey Tsyganov
     date: August, 2011
  contact: atsygan@gmail.com, A.Tsyganov@rug.nl
  (c) 2011, all rights recerved

  Developer of this package is NOT responsible in case of any change of this coe by its users!
  and also NOT responsible for any bugs in the functionality of the package, however support CAN be provided

  This package is developed to make possible version controling during PL/SQL
  development in Oracle.
  Grants needed:
    grant select any table
    grant create job
  to start using Version Controll package you need to execute registration procedure
  for the users you want to use Version Controll:
  execute ats_oravc.oravc_add_user2monitor(<username>);

  then it is nesessary to start the scheduler job:
  oravc_job.oravc_start_plvc_job(<refresh time in minutes>)


  This module uses the folowing modified code of the Java libraries from
  http://www.bmsi.com/java/#diff by Stuart D. Gathman
    Diff.java         - have not been changed
    DiffPrint.java    - have been changed
  Libraries are open and goes under GPL licence
</main_description>

*/

TYPE T_NUMBER_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE T_CLOB_TABLE IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
TYPE T_CLOB_ARRAY IS TABLE OF CLOB;


TYPE T_CLOB_RECORD IS RECORD (
       line_id         NUMBER
      ,line_text       CLOB
     );

TYPE T_CLOB_RECORD_TABLE IS TABLE OF T_CLOB_RECORD INDEX BY BINARY_INTEGER;
TYPE T_CLOB_RECORD_ARRAY IS TABLE OF T_CLOB_RECORD;


-- record to store result from java diff function call
TYPE T_JAVA_REPORT_RECORD IS RECORD (
       doc_number      NUMBER --VARCHAR2(255)
      ,line_number     NUMBER
      ,action          VARCHAR2(255)
      ,line_text       CLOB
    );
TYPE T_JAVA_REPORT_TABLE IS TABLE OF T_JAVA_REPORT_RECORD INDEX BY BINARY_INTEGER;
TYPE T_JAVA_REPORT_ARRAY IS TABLE OF T_JAVA_REPORT_RECORD;

-- report 1 (all-in-one) structures
TYPE T_ORAVC_REPORT_RECORD IS RECORD (
       line_order                       NUMBER
      ,firstobj_id                      oravc_obj.vco_obj_vc_id%TYPE
      ,obj_firstversion_id              NUMBER
      ,obj_firstversion_line_number     NUMBER
      ,obj_firstver_new_line_number     NUMBER
      ,obj_line_unchanged               CLOB
      ,obj_line_deleted                 CLOB
      ,obj_line_added                   CLOB
      ,obj_line_mutated                 VARCHAR2(10)
      ,obj_newversion_id                NUMBER
      ,obj_newversion_line_number       NUMBER
     );

TYPE T_ORAVC_REPORT_TABLE IS TABLE OF T_ORAVC_REPORT_RECORD INDEX BY BINARY_INTEGER;
TYPE T_ORAVC_REPORT_ARRAY IS TABLE OF T_ORAVC_REPORT_RECORD;

-- report 2 (side-by-side) structures
TYPE T_ORAVC_REPORT2_RECORD IS RECORD (
       line_order                    NUMBER
      ,firstobj_id                   NUMBER
      ,obj_firstversion_id           NUMBER
      ,newobj_id                     NUMBER
      ,obj_newversion_id             NUMBER
      ,unchanged                     VARCHAR2(10)
      ,deleted                       VARCHAR2(10)
      ,added                         VARCHAR2(10)
      ,mutated                       VARCHAR2(10)
      ,obj_firstversion_line_number  NUMBER
      ,obj_firstver_new_line_number  NUMBER
      ,obj_firstversion_line_text    CLOB
      ,obj_newversion_line_number    NUMBER
      ,obj_newversion_line_text      CLOB
     );
TYPE T_ORAVC_REPORT2_TABLE IS TABLE OF T_ORAVC_REPORT2_RECORD INDEX BY BINARY_INTEGER;
TYPE T_ORAVC_REPORT2_ARRAY IS TABLE OF T_ORAVC_REPORT2_RECORD;

-- to avoid ussage of GLOBAL variable each variable has its get methond
-- not taged for comments becas it is enternal engine functionality
FUNCTION get_C_PACKAGE_NAME           RETURN VARCHAR2;
FUNCTION get_C_EVENT                  RETURN VARCHAR2;
FUNCTION get_C_ERROR                  RETURN VARCHAR2;
FUNCTION get_C_WARNING                RETURN VARCHAR2;
FUNCTION get_C_ADDED_CHAR             RETURN VARCHAR2;
FUNCTION get_C_ADDED_CHAR2            RETURN VARCHAR2;
FUNCTION get_C_TOCHAR_DATEFORMAT      RETURN VARCHAR2;
FUNCTION get_C_MUTATED                RETURN VARCHAR2;
FUNCTION get_C_DELETED                RETURN VARCHAR2;
FUNCTION get_C_ADDED                  RETURN VARCHAR2;
FUNCTION get_C_CHANGED                RETURN VARCHAR2;
FUNCTION get_C_UNCHANGED              RETURN VARCHAR2;
FUNCTION get_C_JAVAdeleted            RETURN VARCHAR2;
FUNCTION get_C_JAVAinserted           RETURN VARCHAR2;

/*
<comments>
  <function>
    oravc_getVCVersionCode
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes Version Controll object version code its ID
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]   [NUMBER]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_getVCVersionCode (
           p_obj_version_id             NUMBER
         ) RETURN CLOB;


/*
<comments>
  <function>
    oravc_get_obj_source_lines
  </function>

  <return>
    PIPELINED : T_VARCHAR2_RECORD_ARRAY
  </return>

  <description>
    Function returnes array ol lines of the source code. end of line is chr(10)
  </description>

  <parameters>
    <IN>
      [p_clob]    [clob that get lines from]   [NUMBER]
    </IN>
  </parameters>

  <version>
    [1.0] [24-10-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_get_obj_source_lines(
            p_clob                  CLOB
          ) RETURN T_CLOB_RECORD_ARRAY PIPELINED;

/*
<comments>
  <function>
    oravc_get_obj_source_lines
  </function>

  <return>
    PIPELINED : T_VARCHAR2_RECORD_ARRAY
  </return>

  <description>
    Function returnes array ol lines of the source code by its OraVC version id. end of line is chr(10)
  </description>

  <parameters>
    <IN>
      [p_version_id]    [version controll obeject version id]   [NUMBER]
    </IN>
  </parameters>

  <version>
    [1.0] [24-10-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_get_obj_source_lines(
            p_version_id                 NUMBER
          ) RETURN T_CLOB_RECORD_ARRAY PIPELINED;

/*
<comments>
  <procedure>
    setCurrentDiff

  </procedure>

  <description>
    Procedure sets value for the G_TRANSACTION_DIFF variable.
    This variable is used to pass String value from the Java stored procedure as a CLOB.
    It is used as a bridge storage for the diff java function result which calculates difference
    between versions
  </description>

  <parameters>
    <IN>
      [p_diff_value]    [result of the diff java function]   [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE setCurrentDiff(
            p_diff_value              CLOB
          );

/*
<comments>
  <function>
    getCurrentDiff
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes current value of the G_TRANSACTION_DIFF variable.
    This variable is used to pass String value from the Java stored procedure as a CLOB.
    It is used as a bridge storage for the diff java function result which calculates difference
    between versions
  </description>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getCurrentDiff RETURN CLOB;


/*
<comments>
  <procedure>
    oravc_add_user2monitor
  </procedure>

  <description>
    Adds user to the list which must be monitored
    Inserts new linbe into oravc_usr2monitor
  </description>

  <parameters>
    <IN>
      [p_ora_username]    [oracle username to be added]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_add_user2monitor(
             p_ora_username           VARCHAR2
          );

/*
<comments>
  <procedure>
    oravc_delete_user2monitor
  </procedure>

  <description>
    delets user from the list which must be monitored
  </description>

  <parameters>
    <IN>
      [p_ora_username]    [oracle username to be deleted]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_delete_user2monitor(
             p_ora_username           VARCHAR2
          );

/*
<comments>
  <procedure>
    oravc_add_objtype2monitor
  </procedure>

  <description>
    Adds objtype to the list which must be monitored
    Adds record to oravc_objtype2monitor table. It contains names of the Oracle Object types
    like: 'PACKAGE', 'PACKAGE BODY' also possible 'TRIGGERS', 'TYPE' etc...
  </description>

  <parameters>
    <IN>
      [p_ora_objtype]    [oracle object type name like: 'PACKAGE' etc...]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_add_objtype2monitor(
             p_ora_objtype            VARCHAR2
          );

/*
<comments>
  <procedure>
    oravc_delete_objtype2monitor
  </procedure>

  <description>
    delets objtype from the list which must be monitored
    Deletes record from oravc_objtype2monitor table witch contains name of the Oracle Object type
    like: 'PACKAGE', 'PACKAGE BODY' also possible 'TRIGGERS', 'TYPE' etc...
  </description>

  <parameters>
    <IN>
      [p_ora_objtype]    [oracle object type name like: 'PACKAGE' etc...]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_delete_objtype2monitor(
             p_ora_objtype            VARCHAR2
          );

/*
<comments>
  <procedure>
    oravc_add_event2monitor
  </procedure>

  <description>
    Adds event from the list which must be monitored
    Adds record from oravc_event2monitor table witch contains names of the Oracle system events
    to monitor by version controll tool
  </description>

  <parameters>
    <IN>
      [p_ora_event]    [oracle system event name like: 'CREATE' etc...]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_add_event2monitor(
             p_ora_event            VARCHAR2
          );

/*
<comments>
  <procedure>
    oravc_delete_event2monitor
  </procedure>

  <description>
    delets event from the list which must be monitored
    Deletes record from oravc_event2monitor table witch contains names of the Oracle system events
    to monitor by version controll tool
  </description>

  <parameters>
    <IN>
      [p_ora_event]    [oracle system event name like: 'CREATE' etc...]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_delete_event2monitor(
             p_ora_event            VARCHAR2
          );





/*
<comments>
  <procedure>
    oravc_main_vc
  </procedure>

  <description>
    This procedure is a main entrance into version control
    It is used for identifying if any new version is need. Used in the SCHEDULER job.
    It checke if there were any new objects created for the registered users and onbject types.
    Then checkes if there were any changes in the already existing objects in Version Controll.
    Check is made by the comparison of the source codes. DDL time is not right,
    because object could be compiled by no changes were made.
    If p_setcleanvers_olderthen parameter is not null then
      cleans old data lower {sysdate - p_setcleanvers_olderthen_day}
      but never deletes last version of the object
    by default p_setcleanvers_olderthen_day is null

  </description>

  <parameters>
    <IN>
      [p_owner]                         [oracle object owner]                                 [VARCHAR2]  [NULL]
      [p_objname]                       [oracle obkect name]                                  [VARCHAR2]  [NULL]
      [p_objtype]                       [object type: 'PACKAGE', 'PACKAGE BODY' etc]          [VARCHAR2]  [NULL]
      [p_obj_versioninfo]               [version information. Engine sends it fro mtrigger]   [NUMBER]    [NULL]
      [p_setcleanvers_olderthen_day]    [number of days which indicates old version]          [NUMBER]    [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
    [1.1] [24-10-2012] [Andrey Tsyganov] [modification] [p_obj_versioninfo was added]
  </version>

</comments>
*/
PROCEDURE oravc_main_vc(
             p_owner                          VARCHAR2 := NULL
            ,p_objname                        VARCHAR2 := NULL
            ,p_objtype                        VARCHAR2 := NULL
            ,p_setcleanvers_olderthen_day     NUMBER   := NULL
            ,p_obj_versioninfo                VARCHAR2 := NULL
          );

/*
<comments>
  <function>
    oravc_get_2versions_diff(
  </function>

  <return>
    CLOB
  </return>

  <description>
    function returnes difference of two clobs
    It is main function to analyze difference in versions of the stored objects
    Uses implementation uses versions id's and versions's CLOB's as an input parameters
    Possible display modes for parameter p_display
      1: EdPrint
      2: ContextPrint
      3: UnifiedPrint
      4: ReportPrint
      if somethinh else then default 0: NormalPrint

  </description>

  <parameters>
    <IN>
      [p_version1]          [version 1 to compare]                      [NUMBER]
      [p_version2]          [version 2 to compare]                      [NUMBER]
      [p_display]           [display mode]                              [NUMBER]
      [p_version1_clob]     [clob 1 for direct clob difference check]   [CLOB]
      [p_version2_clob]     [clob 2 for direct clob difference check]   [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_get_2versions_diff(
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
           ,p_display                   NUMBER := 0
         ) RETURN CLOB;

/*
<comments>
  <function>
    toSortJavaDiffTable
  </function>

  <return>
    T_JAVA_REPORT_ARRAY PIPELINED
  </return>

  <description>
    PIPELINED Function to be able easy sort retuned Java Diff structure
  </description>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION toSortJavaDiffTable RETURN T_JAVA_REPORT_ARRAY PIPELINED;

/*
<comments>
  <function>
    oravc_allINone_diff
  </function>

  <return>
    T_ORAVC_REPORT_ARRAY PIPELINED
  </return>

  <description>
    PIPELINED function returnes array for report about difference in two versions of packages, or in two CLOBs
    to use in SQL
    all-in-one style
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]    [0]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_version2_clob]     [clob 2 for direct clob difference check]         [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_allINone_diff (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN T_ORAVC_REPORT_ARRAY  PIPELINED;

/*
<comments>
  <function>
    oravc_sideBYside_diff
  </function>

  <return>
    T_ORAVC_REPORT2_ARRAY PIPELINED
  </return>

  <description>
    PIPELINED function returnes array for report2 about difference in two versions of packages, or in two CLOBs
    This is syde-by-sied style of report
    To use in SQL
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]    [0]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_version2_clob]     [clob 2 for direct clob difference check]         [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_sideBYside_diff (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN T_ORAVC_REPORT2_ARRAY PIPELINED;

/*
<comments>
  <function>
    allINone_diff_clob
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes full text of package with difference in two versions, or full list of differences in two CLOBs
    all-in-one report type
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]    [0]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_version2_clob]     [clob 2 for direct clob difference check]         [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION allINone_diff_clob (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN CLOB;

/*
<comments>
  <function>
    sideBYside_diff_clob
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes full text of package with difference in two versions, or full list of differences in two CLOBs
    side-by-side report
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]    [0]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_version2_clob]     [clob 2 for direct clob difference check]         [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION sideBYside_diff_clob (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
        ) RETURN CLOB;

/*
<comments>
  <function>
    allINone_diff_htmlTable
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes clob with HTML table for report1 where
        DELETED lines marked as RED
        ADDED   lines as LIGTH YELLOW
    all-in-one report type
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_version_id1]       [version controll obeject version1 id]                    [NUMBER]
      [p_version_id2]       [version controll obeject version2 id]                    [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]           [NUMBER]    [0]
      [p_show_title]        [show title of the report: 1= true, else false]           [NUMBER]    [1]
      [p_show_header]       [show table header for the report: 1= true, else false]   [CLOB]      [NULL]
      [p_version1_clob]     [clob 1 for direct clob difference check]                 [CLOB]      [NULL]
      [p_version2_clob]     [clob 2 for direct clob difference check]                 [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION allINone_diff_htmlTable (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_diff_only                 NUMBER := 0
           ,p_version_generator         NUMBER := 0
           ,p_show_title                NUMBER := 1
           ,p_show_header               NUMBER := 1
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN CLOB;

/*
<comments>
  <function>
    sideBYside_diff_htmlTable
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes clob with HTML table for report2 where
        DELETED lines marked as RED
        ADDED   lines as LIGTH YELLOW
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_version_id1]       [version controll obeject version1 id]                    [NUMBER]
      [p_version_id2]       [version controll obeject version2 id]                    [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]           [NUMBER]    [0]
      [p_show_title]        [show title of the report: 1= true, else false]           [NUMBER]    [1]
      [p_show_header]       [show table header for the report: 1= true, else false]   [CLOB]      [NULL]
      [p_version1_clob]     [clob 1 for direct clob difference check]                 [CLOB]      [NULL]
      [p_version2_clob]     [clob 2 for direct clob difference check]                 [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION sideBYside_diff_htmlTable (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_show_title                NUMBER := 1
           ,p_show_header               NUMBER := 1
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN CLOB;


/*
<comments>
  <procedure>
    allINone_diff_totable
  </procedure>

  <description>
    Mainlly ussed for debug perpose to test display of the all-in-one report style
    however can be used to store report result into table
    and then query in with SQL
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]    [0]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_version2_clob]     [clob 2 for direct clob difference check]         [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE allINone_diff_totable (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         );

/*
<comments>
  <procedure>
    sideBYside_diff_totable
  </procedure>

  <description>
    Mainlly ussed for debug perpose to test display of the side-by-side report style
    however can be used to store report result into table
    and then query in with SQL
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]    [0]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_version2_clob]     [clob 2 for direct clob difference check]         [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE sideBYside_diff_totable (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         );

/*
<comments>
  <function>
    replaceHTMLspecialChars
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function to replace special HTML characters for Version Controll html reports
    Implementation for CLOB
  </description>

  <parameters>
    <IN>
      [p_string]    [string to replace special characteer]   [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION replaceHTMLspecialChars(
            p_clob                CLOB
         ) RETURN CLOB;


/*
<comments>
  <function>
    getClobForHTML
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function to make CLOB HTML print from one place
  </description>

  <parameters>
    <IN>
      [p_clob]    [clob to process]   [CLOB]
      [p_id]      [to put specific id for result HTML table if needed]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getClobForHTML(
            p_clob                      CLOB
           ,p_id                        VARCHAR2 := NULL
         ) RETURN CLOB;

/*
<comments>
  <procedure>
    add2log
  </procedure>

  <description>
     Procedure puts message into Version Controll log table
  </description>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE add2log(
            p_message_type                    VARCHAR2
           ,p_message_text                    CLOB
           ,p_message_place                   VARCHAR2
          );

function oravc_get_obj_source(
             p_objowner                  VARCHAR2
            ,p_objname                   VARCHAR2
            ,p_objtype                   VARCHAR2
          ) RETURN CLOB;

END ORAVC_ENGINE;
/


CREATE OR REPLACE PACKAGE BODY ORAVC_ENGINE AS

C_TRIGGER_TEMPLATE_TAG1             CONSTANT VARCHAR2(255) := '<###TRIGGER_NAME###>';
C_TRIGGER_TEMPLATE_TAG2             CONSTANT VARCHAR2(255) := '<###SCHEMA_NAME###>';

G_TRANSACTION_DIFF          CLOB;
G_javaDiffTable             T_JAVA_REPORT_TABLE;

C_PACKAGE_NAME                                   CONSTANT VARCHAR2(255) := 'ORAVC_ENGINE';
C_TOCHAR_DATEFORMAT                              CONSTANT VARCHAR2(255) := 'dd-mm-yyyy hh24:mi:ss';

C_EVENT                                          CONSTANT VARCHAR2(255) := 'EVENT';
C_ERROR                                          CONSTANT VARCHAR2(255) := 'ERROR';
C_WARNING                                        CONSTANT VARCHAR2(255) := 'WARNING';

-- General Version Control parameters
C_ADDED_CHAR                                     CONSTANT VARCHAR2(255) := '<#LINE ADDED BY VERSION CONTROLL#>' || chr(10);
C_ADDED_CHAR2                                    CONSTANT VARCHAR2(255) := 'ACHARFORCSS' || chr(10);
C_MUTATED                                        CONSTANT VARCHAR2(255) := 'MUTATE';
C_DELETED                                        CONSTANT VARCHAR2(255) := 'DELETED';
C_ADDED                                          CONSTANT VARCHAR2(255) := 'ADDED';
C_CHANGED                                        CONSTANT VARCHAR2(255) := 'CHANGED';
C_UNCHANGED                                      CONSTANT VARCHAR2(255) := 'UNCHANGED';
C_JAVAdeleted                                    CONSTANT VARCHAR2(255) := 'deleted';
C_JAVAinserted                                   CONSTANT VARCHAR2(255) := 'inserted';

-- to avoid ussage of GLOBAL variable each variable has its get methond
-- not taged for comments becas it is enternal engine functionality
FUNCTION get_C_PACKAGE_NAME           RETURN VARCHAR2 IS BEGIN RETURN C_PACKAGE_NAME;           END;
FUNCTION get_C_EVENT                  RETURN VARCHAR2 IS BEGIN RETURN C_EVENT;                  END;
FUNCTION get_C_ERROR                  RETURN VARCHAR2 IS BEGIN RETURN C_ERROR;                  END;
FUNCTION get_C_WARNING                RETURN VARCHAR2 IS BEGIN RETURN C_WARNING;                END;
FUNCTION get_C_ADDED_CHAR             RETURN VARCHAR2 IS BEGIN RETURN C_ADDED_CHAR;             END;
FUNCTION get_C_ADDED_CHAR2            RETURN VARCHAR2 IS BEGIN RETURN C_ADDED_CHAR2;            END;
FUNCTION get_C_TOCHAR_DATEFORMAT      RETURN VARCHAR2 IS BEGIN RETURN C_TOCHAR_DATEFORMAT;      END;
FUNCTION get_C_MUTATED                RETURN VARCHAR2 IS BEGIN RETURN C_MUTATED;                END;
FUNCTION get_C_DELETED                RETURN VARCHAR2 IS BEGIN RETURN C_DELETED;                END;
FUNCTION get_C_ADDED                  RETURN VARCHAR2 IS BEGIN RETURN C_ADDED;                  END;
FUNCTION get_C_CHANGED                RETURN VARCHAR2 IS BEGIN RETURN C_CHANGED;                END;
FUNCTION get_C_UNCHANGED              RETURN VARCHAR2 IS BEGIN RETURN C_UNCHANGED;              END;
FUNCTION get_C_JAVAdeleted            RETURN VARCHAR2 IS BEGIN RETURN C_JAVAdeleted;            END;
FUNCTION get_C_JAVAinserted           RETURN VARCHAR2 IS BEGIN RETURN C_JAVAinserted;           END;

function oravc_get_obj_source(
             p_objowner                  VARCHAR2
            ,p_objname                   VARCHAR2
            ,p_objtype                   VARCHAR2
          ) RETURN CLOB IS
  l_function_name                    VARCHAR2(255) := 'oravc_get_obj_source';
  p_res_source             CLOB;

BEGIN
  p_res_source := NULL;

  SELECT DBMS_METADATA.GET_DDL( decode(p_objtype, 'JAVA SOURCE', 'JAVA_SOURCE'
                                                , 'MATERIALIZED VIEW', 'MATERIALIZED_VIEW'
                                                , 'MATERIALIZED VIEW LOG', 'MATERIALIZED_VIEW_LOG'
                                                , 'PACKAGE', 'PACKAGE_SPEC'
                                                , 'PACKAGE BODY', 'PACKAGE_BODY'
                                      ,p_objtype
                                      )
                               ,p_objname
                               ,p_objowner
                              )
    INTO p_res_source
    FROM dual;

  return p_res_source;
END oravc_get_obj_source;

/*
<comments>
  <function>
    oravc_get_next_objid
  </function>

  <return>
    NUMBER
  </return>

  <description>
    get next internal oravc obj_id from the sequence
  </description>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_get_next_objid RETURN NUMBER
  IS

  l_return              NUMBER;
BEGIN
  SELECT sq_obj_vc_id.NEXTVAL
    INTO l_return
    FROM dual;

  RETURN l_return;
END oravc_get_next_objid;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE add2log(
            p_message_type                    VARCHAR2
           ,p_message_text                    CLOB
           ,p_message_place                   VARCHAR2
          ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;

  l_id                                        NUMBER;
BEGIN
  l_id := oravc_get_next_objid;
  INSERT INTO oravc_log(
                vcl_id
               ,vcl_datetime
               ,vcl_message_type
               ,vcl_message_text
               ,vcl_message_place
               )
         VALUES(l_id
               ,SYSDATE
               ,p_message_type
               ,SUBSTR(p_message_text, 1, 4000)
               ,p_message_place
               );
  COMMIT;
END add2log;

/*
<comments>
  <function>
    appendSourceWithVCChar
  </function>

  <return>
    CLOB
  </return>

  <description>
    adds special label to source(CLOB) to identify OraVC object source.
  </description>

  <version>
    [1.0] [26-10-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION appendSourceWithVCChar(
           p_source             CLOB
         ) RETURN CLOB IS
  l_result                      CLOB;
BEGIN
  l_result := p_source;
  -- now lets add special line to the source clob.
  -- this will garantie that all version has at least one similarity
  IF SUBSTR(l_result, LENGTH(l_result), 1) = chr(10) THEN
    l_result := l_result || C_ADDED_CHAR;
  ELSE
    l_result := l_result || chr(10) || C_ADDED_CHAR;
  END IF;

  RETURN l_result;
END;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION replaceHTMLspecialChars(
            p_clob                CLOB
         ) RETURN CLOB IS
  l_result                        CLOB;
BEGIN
  l_result := p_clob;
  l_result := REPLACE(l_result, '&', '&amp;');
  l_result := REPLACE(l_result, '<', '&lt;');
  l_result := REPLACE(l_result, '>', '&gt;');
  l_result := REPLACE(l_result, '"', '&quot;');
  l_result := REPLACE(l_result, chr(9), '&#09;');
  l_result := REPLACE(l_result, ' ', '&nbsp;');

  RETURN l_result;

END replaceHTMLspecialChars;


/*
<comments>
  in package specification

</comments>
*/
FUNCTION getClobForHTML(
            p_clob                      CLOB
           ,p_id                        VARCHAR2 := NULL
         ) RETURN CLOB IS

  l_clob                                CLOB;

BEGIN
  --l_clob := REPLACE(p_clob, chr(9), '  ');
  l_clob := p_clob;
  l_clob := replaceHTMLspecialChars(l_clob);
  l_clob := REPLACE(l_clob, chr(10), '<br/>');
  l_clob := '<table ' || p_id || '><tr><td nowrap><code> ' || l_clob || '</code></td><tr></table>';

  RETURN l_clob;
END getClobForHTML;

/*
<comments>
  <function>
    oravc_getVCVersionCodeArray
  </function>

  <return>
    T_VARCHAR2_TABLE
  </return>

  <description>
    Function returnes array of code lines from clob
  </description>

  <parameters>
    <IN>
      [p_clob]    [source clob]   [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [24-10-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_getVCVersionCodeArray (
           p_clob                       CLOB
         ) RETURN T_CLOB_TABLE IS
  l_result                              T_CLOB_TABLE;
  l_clob                                CLOB;
  l_temp_line                           CLOB := '';
BEGIN
  l_clob := p_clob;
  IF SUBSTR(l_clob, LENGTH(l_clob), 1) <> CHR(10) THEN
    l_clob := l_clob || chr(10);
  END IF;

  WHILE INSTR(l_clob, chr(10)) > 0 LOOP
    l_temp_line := SUBSTR(l_clob, 1, INSTR(l_clob, chr(10)));
    l_clob := SUBSTR(l_clob, INSTR(l_clob, chr(10)) + 1, length(l_clob));
    l_result(l_result.COUNT+1) := l_temp_line;

  END LOOP;

  RETURN l_result;

END oravc_getVCVersionCodeArray;

/*
<comments>
  <procedure>
    oravc_get_obj_source
  </procedure>

  <description>
    Fetches the source code of the particular object from the
    internal Oracle DB structures
  </description>

  <parameters>
    <IN>
      [p_objowner]          [oracle object owner]                         [VARCHAR2]
      [p_objname]           [oracle obkect name]                          [VARCHAR2]
      [p_objtype]           [object type: 'PACKAGE', 'PACKAGE BODY' etc]  [VARCHAR2]
    </IN>
    <OUT>
      [p_res_source]        [full object source]            [CLOB]
    <OUT>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
    [1.1] [24-10-2012] [Andrey Tsyganov] [modification] [DBMS_METADATA.GET_DDL now used to get object source]
  </version>

</comments>
*/
PROCEDURE oravc_get_obj_source(
             p_objowner                  VARCHAR2
            ,p_objname                   VARCHAR2
            ,p_objtype                   VARCHAR2
            ,p_res_source            OUT CLOB
          ) IS
  l_function_name                    VARCHAR2(255) := 'oravc_get_obj_source';

BEGIN
  p_res_source := NULL;

  SELECT DBMS_METADATA.GET_DDL( decode(p_objtype, 'JAVA SOURCE', 'JAVA_SOURCE'
                                                , 'MATERIALIZED VIEW', 'MATERIALIZED_VIEW'
                                                , 'MATERIALIZED VIEW LOG', 'MATERIALIZED_VIEW_LOG'
                                                , 'PACKAGE', 'PACKAGE_SPEC'
                                                , 'PACKAGE BODY', 'PACKAGE_BODY'
                                      ,p_objtype
                                      )
                               ,p_objname
                               ,p_objowner
                              )
    INTO p_res_source
    FROM dual;

EXCEPTION
  WHEN OTHERS THEN
    add2log(
       p_message_type    => C_WARNING
      ,p_message_text    => 'WARNING IN ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      || SQLCODE || chr(10) || l_function_name    || chr(10)
      || '    ,p_owner => '                       || p_objowner                   || chr(10)
      || '    ,p_objtype => '                     || p_objtype                    || chr(10)
      || '    ,p_objname => '                     || p_objname                    || chr(10)
      || SQLERRM
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );

    p_res_source := 'NO DATA FOUND BY DBMS_METADATA.GET_DDL' || CHR(10);

END oravc_get_obj_source;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_get_obj_source_lines(
            p_clob                  CLOB
          ) RETURN T_CLOB_RECORD_ARRAY PIPELINED IS
  l_line                                 T_CLOB_RECORD;
  l_lines                                T_CLOB_TABLE;
BEGIN
  l_lines := oravc_getVCVersionCodeArray(p_clob);
  FOR iLine IN 1..l_lines.COUNT LOOP
    l_line.line_id := iLine;
    l_line.line_text := l_lines(iLine);
    PIPE ROW ( l_line );
  END LOOP;
END oravc_get_obj_source_lines;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_get_obj_source_lines(
            p_version_id                 NUMBER
          ) RETURN T_CLOB_RECORD_ARRAY PIPELINED IS

  l_source                               CLOB;
  l_line                                 T_CLOB_RECORD;
  l_lines                                T_CLOB_TABLE;
BEGIN

  SELECT ovm_obj_source
    INTO l_source
    FROM oravc_obj_version_main
   WHERE ovm_obj_version_id = p_version_id;

  l_lines := oravc_getVCVersionCodeArray(l_source);
  FOR iLine IN 1..l_lines.COUNT LOOP
    l_line.line_id := iLine;
    l_line.line_text := l_lines(iLine);
    PIPE ROW ( l_line );
  END LOOP;
END oravc_get_obj_source_lines;


/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_getVCVersionCode (
           p_obj_version_id             NUMBER
         ) RETURN CLOB IS
    l_res                               CLOB := NULL;
BEGIN
  SELECT ovm_obj_source
    INTO l_res
    FROM oravc_obj_version
   WHERE ovm_obj_version_id = p_obj_version_id;

  RETURN l_res;
END oravc_getVCVersionCode;


/*
<comments>
  in package specification

</comments>
*/
PROCEDURE setCurrentDiff(
            p_diff_value                CLOB
          ) IS
BEGIN
  G_TRANSACTION_DIFF := p_diff_value;
END setCurrentDiff;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION getCurrentDiff RETURN CLOB IS
BEGIN
  RETURN G_TRANSACTION_DIFF;
END getCurrentDiff;

/*
<comments>
  <procedure>
    javaDiff
  </procedure>

  <description>
    Procedure which calls java diff function fro mthe Java stored procedure.
    this java code sets G_TRANSACTION_DIFF variable.
    Result is comparation of two CLObs.
    Possible display modes for parameter p_pStyle
      1: EdPrint
      2: ContextPrint
      3: UnifiedPrint
      if somethinh else then default 0: NormalPrint

  </description>

  <parameters>
    <IN>
      [p_pClobA]    [first Clob]                [CLOB]
      [p_pClobB]    [second Clob]               [CLOB]
      [p_pStyle]    [result display mode]       [number]
      [p_pName1]    [hame or id of the CLOB1]   [VARCHAR2]
      [p_pName2]    [hame or id of the CLOB2]   [VARCHAR2]

    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE javaDiff (
             p_pClobA                 CLOB
            ,p_pClobB                 CLOB
            ,p_pStyle                 NUMBER
            ,p_pName1                 VARCHAR2
            ,p_pName2                 VARCHAR2
          ) IS

  LANGUAGE JAVA NAME 'OraVersionControllDiff.diff(java.sql.Clob, java.sql.Clob, int, java.lang.String, java.lang.String)';


/*
<comments>
  <function>
    getFullVCObjectName
  </function>

  <return>
    VARCHAR2
  </return>

  <description>
    Funtion returnes full description of the Version Controll object
  </description>

  <parameters>
    <IN>
      [p_obj_id]          [version controll object id]    [oravc_obj.vco_obj_vc_id%TYPE]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getFullVCObjectName(
            p_obj_id                          oravc_obj.vco_obj_vc_id%TYPE
        ) RETURN VARCHAR2 IS
  l_obj_desc                                  VARCHAR2(255);

BEGIN
  IF p_obj_id IS NOT NULL THEN
    SELECT vco_obj_owner || '.' || vco_obj_name || ':' || vco_obj_type
      INTO l_obj_desc
      FROM oravc_obj
    WHERE vco_obj_vc_id = p_obj_id;

  END IF;

  RETURN l_obj_desc;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'Info unavailable for object id:' || p_obj_id;
END getFullVCObjectName;

/*
<comments>
  <function>
    getVCObjectVersionInfo
  </function>

  <return>
    VARCHAR2
  </return>

  <description>
    Function returnes description of the version of the Version Controll object
  </description>

  <parameters>
    <IN>
      [p_version_id]          [version controll object version id]    [oravc_obj_version.ovm_obj_version_id%TYPE]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getVCObjectVersionInfo(
            p_version_id                          oravc_obj_version.ovm_obj_version_id%TYPE
        ) RETURN VARCHAR2 IS
  l_vers_desc                                  VARCHAR2(255);

BEGIN
  SELECT    'version:' || ovm_obj_version_id || '; creation time:' || to_char(ovm_obj_version_date, C_TOCHAR_DATEFORMAT)
         || '; ddl time:' || to_char(ovm_obj_ver_ddl_creattime, C_TOCHAR_DATEFORMAT)
         || '; previous version:' || ovm_obj_parent || ';' || ovm_obj_versioninfo
    INTO l_vers_desc
    FROM oravc_obj_version
   WHERE ovm_obj_version_id = p_version_id;

  RETURN l_vers_desc;
EXCEPTION
  WHEN OTHERS THEN
    RETURN p_version_id;
END getVCObjectVersionInfo;

/*
<comments>
  <function>
    getVCObjectIDByVersion
  </function>

  <return>
    NUMBER
  </return>

  <description>
    Function returnes OraVC object id by its version
  </description>

  <parameters>
    <IN>
      [p_version_id]          [version controll object version id]    [oravc_obj_version.ovm_obj_version_id%TYPE]
    </IN>
  </parameters>

  <version>
    [1.0] [26-10-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getVCObjectIDByVersion(
            p_version_id                          oravc_obj_version.ovm_obj_version_id%TYPE
        ) RETURN VARCHAR2 IS
  l_ob_id                                  VARCHAR2(255);

BEGIN
  SELECT ovm_obj_vc_id
    INTO l_ob_id
    FROM oravc_obj_version
   WHERE ovm_obj_version_id = p_version_id;

  RETURN l_ob_id;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END getVCObjectIDByVersion;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_add_user2monitor(
             p_ora_username           VARCHAR2
          ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO oravc_usr2monitor(
                vc_ora_user
              )
       VALUES (UPPER(p_ora_username)
              );

  COMMIT;
END oravc_add_user2monitor;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_delete_user2monitor(
             p_ora_username           VARCHAR2
          ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DELETE oravc_usr2monitor
   WHERE vc_ora_user = p_ora_username;
  COMMIT;
END oravc_delete_user2monitor;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_add_objtype2monitor(
             p_ora_objtype            VARCHAR2
          ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO oravc_objtype2monitor(
                vc_ora_objtype
              )
       VALUES (UPPER(p_ora_objtype)
              );

  COMMIT;
END oravc_add_objtype2monitor;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_delete_objtype2monitor(
             p_ora_objtype            VARCHAR2
          ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
  DELETE oravc_objtype2monitor
   WHERE vc_ora_objtype = p_ora_objtype;

  COMMIT;
END oravc_delete_objtype2monitor;


/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_add_event2monitor(
             p_ora_event            VARCHAR2
          ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO oravc_event2monitor(
                vc_ora_event
              )
       VALUES (UPPER(p_ora_event)
              );

  COMMIT;
END oravc_add_event2monitor;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_delete_event2monitor(
             p_ora_event            VARCHAR2
          ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
  DELETE oravc_event2monitor
   WHERE vc_ora_event = p_ora_event;

  COMMIT;
END oravc_delete_event2monitor;

/*
<comments>
  <function>
    oravc_get_next_versionid
  </function>

  <returnes>
    NUMBER
  </returnes>

  <description>
    get next internal oravc version_id from the sequence
  </description>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_get_next_versionid RETURN NUMBER
  IS

  l_return             NUMBER;
BEGIN
  SELECT sq_obj_version_id.NEXTVAL
    INTO l_return
    FROM dual;

  RETURN l_return;
END oravc_get_next_versionid;

/*
<comments>
  <function>
    oravc_oravc_obj_insert
  </function>

  <return>
    oravc_obj.vco_obj_vc_id%TYPE
  </return>

  <description>
    insert record in the oravc_obj table.
    Registration of the objects for the particular user.
    Returnes newli registerd OVersion Controll object id
  </description>

  <parameters>
    <IN>
      [p_objowner]          [oracle object owner]                         [VARCHAR2]
      [p_objname]           [oracle obkect name]                          [VARCHAR2]
      [p_objtype]           [object type: 'PACKAGE', 'PACKAGE BODY' etc]  [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_oravc_obj_insert(
            p_obj_owner                       VARCHAR2
           ,p_obj_name                        VARCHAR2
           ,p_obj_type                        VARCHAR2
        ) RETURN oravc_obj.vco_obj_vc_id%TYPE IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_obj_vc_id                                 NUMBER;

BEGIN
  l_obj_vc_id := oravc_get_next_objid;
  INSERT INTO oravc_obj(
                vco_obj_owner
               ,vco_obj_name
               ,vco_obj_type
               ,vco_obj_vc_id
               )
       VALUES (p_obj_owner
              ,p_obj_name
              ,p_obj_type
              ,l_obj_vc_id
              );
  COMMIT;
  RETURN l_obj_vc_id;
END oravc_oravc_obj_insert;

/*
<comments>
  <procedure>
    oravc_oravc_version_insert
  </procedure>

  <return>
    oravc_obj_version.obj_version_id%TYPE
  <return>

  <description>
    insert record in the oravc_obj_version table
    Creates new version for the object. Saves difference between new version and the last one saved if exists
    This function returnes new object version id.

    Main specific is that to each version source this procedure add C_ADDED_CHAR line.
    This is to be sure during diff operation that there is at leas ONE similarity in the source code.
    Espetially it can be important for comparetion of two different versions fro mthe different branches
  </description>

  <parameters>
    <IN>
      [p_obj_vc_id]             [new version id]  [NUMBER]
      [p_obj_version_date]      [date/time of version creation]  [DATE]
      [p_obj_source]            [object source]  [CLOB]
      [p_obj_parent]            [object parent id (if <= 0 then will be NULL]  [NUMBER]
      [p_obj_ver_ddl_creattime] [time of the last DDL manipulation on the object]  [DATE]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_oravc_version_insert(
            p_obj_vc_id                     NUMBER
           ,p_obj_version_date              DATE
           ,p_obj_source                    CLOB
           ,p_obj_parent                    NUMBER
           ,p_obj_ver_ddl_creattime         DATE
           ,p_obj_versioninfo               VARCHAR2
         ) RETURN oravc_obj_version.ovm_obj_version_id%TYPE IS
  PRAGMA AUTONOMOUS_TRANSACTION;

  l_obj_version_id                          NUMBER;
  l_diff_clob                               CLOB;
  l_parent_id                               NUMBER;
  l_source                                  CLOB;

BEGIN
  l_obj_version_id := oravc_get_next_versionid;
  l_source := appendSourceWithVCChar(p_obj_source);

  IF p_obj_parent > 0 THEN
    l_parent_id := p_obj_parent;
  ELSE
    l_parent_id := NULL;
  END IF;

  INSERT INTO oravc_obj_version(
                ovm_obj_vc_id
               ,ovm_obj_version_date
               ,ovm_obj_source
               ,ovm_obj_parent
               ,ovm_obj_version_id
               ,ovm_obj_ver_ddl_creattime
               ,ovm_obj_versioninfo
              )
       VALUES ( p_obj_vc_id
               ,p_obj_version_date
               ,l_source
               ,l_parent_id
               ,l_obj_version_id
               ,p_obj_ver_ddl_creattime
               ,p_obj_versioninfo
              );

  -- now we need to add source lines for this version
  FOR iLine IN (
            SELECT *
              FROM TABLE(oravc_engine.oravc_get_obj_source_lines(l_source))
           )
  LOOP
    INSERT INTO oravc_obj_version_lines(
                ovl_version_id
               ,ovl_line_id
               ,ovl_line_source
              )
       VALUES ( l_obj_version_id
               ,iLine.line_id
               ,iLine.line_text
              );

  END LOOP;

  COMMIT;

  RETURN l_obj_version_id;
END oravc_oravc_version_insert;

/*
<comments>
  <function>
    getJavaRecord
  </function>

  <return>
    T_JAVA_REPORT_RECORD
  </return>

  <description>
    Function split the string from the Java CLOB report type 4 and returnes record of the values
  </description>

  <parameters>
    <IN>
      [p_string]    [string to split]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getJavaRecord(
           p_string                     CLOB
         ) RETURN T_JAVA_REPORT_RECORD IS
  l_result                           T_JAVA_REPORT_RECORD;
BEGIN
  l_result.doc_number  :=   TO_NUMBER(SUBSTR(p_string, 1                             , INSTR(p_string, '<', 1, 1) - 1));
  l_result.line_number := TO_NUMBER(
                            SUBSTR(p_string, INSTR(p_string, '<', 1, 1) + 1, INSTR(p_string, '<', 1, 2) - 2 - LENGTH(l_result.doc_number))

                          );
  l_result.action      :=   SUBSTR(p_string, INSTR(p_string, '<', 1, 2) + 1, INSTR(p_string, '<', 1, 3) - 3 - LENGTH(l_result.doc_number) - LENGTH(l_result.line_number));
  l_result.line_text   :=   SUBSTR(p_string, INSTR(p_string, '<', 1, 3) + 1, LENGTH(p_string));
  -- here +1 because Java counts arrays from [0] element
  l_result.line_number := l_result.line_number + 1;
  --l_result.line_text   := NVL(REPLACE(l_result.line_text, chr(10)), ' ');
  RETURN l_result;
END;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION toSortJavaDiffTable RETURN T_JAVA_REPORT_ARRAY PIPELINED
  IS
BEGIN
  FOR i IN 1..G_javaDiffTable.COUNT LOOP
    PIPE ROW (G_javaDiffTable(i));
  END LOOP;

  RETURN;
END toSortJavaDiffTable;

/*
<comments>
  <function>
    getTableFromJavaDiff
  </function>

  <return>
    T_JAVA_REPORT_TABLE
  </return>

  <description>
    Function returnes table of records from Java CLOB report type 4
    each table record of T_JAVA_REPORT_RECORD type
  </description>

  <parameters>
    <IN>
      [p_diffclob]    [Clob to extract data]   [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getTableFromJavaDiff(
           p_diffclob                   CLOB
          ,p_action_filter              VARCHAR2 := NULL
          ,p_docnumber_filter           VARCHAR2 := NULL
         ) RETURN T_JAVA_REPORT_TABLE
  IS

  l_result                              T_JAVA_REPORT_TABLE;
  l_tmp_record                          T_JAVA_REPORT_RECORD;
  l_clob                               CLOB;
  l_temp_number                         NUMBER;
BEGIN
  l_clob := p_diffclob;
  IF SUBSTR(l_clob, LENGTH(l_clob), 1) <> CHR(10) THEN
    l_clob := l_clob || chr(10);
  END IF;

  WHILE LENGTH(l_clob) > 0 LOOP
    l_tmp_record := getJavaRecord(SUBSTR(l_clob, 1, INSTR(l_clob, chr(10))));
    IF    (p_action_filter    IS NULL OR l_tmp_record.action = p_action_filter)
      AND (p_docnumber_filter IS NULL OR l_tmp_record.doc_number = p_docnumber_filter)
    THEN
      l_result(l_result.COUNT + 1) := l_tmp_record;
    END IF;
    l_clob := SUBSTR(l_clob, INSTR(l_clob, chr(10)) + 1, LENGTH(l_clob) - LENGTH(l_result(l_result.COUNT).line_text) + 1);
  END LOOP;

  RETURN l_result;
END getTableFromJavaDiff;

/*
<comments>
  <function>
    diffArrayBuild
  </function>

  <return>
    T_ORAVC_REPORT_TABLE
  </return>

  <description>
    Function returnes array for report about difference in two versions of packages
    this particular function restructures result array for events detected with Java Diff call
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
    [1.1] [01-03-2012] [Andrey Tsyganov] [bugfix]   [extra loop was done on array and lead to a bit wrong result]
  </version>

</comments>
*/
FUNCTION diffArrayBuild (
          p_ovm_obj_parent              NUMBER
         ,p_ovm_obj_version_id          NUMBER
         ,p_version1_clob               CLOB
         ,p_version2_clob               CLOB
         ,p_array                       T_ORAVC_REPORT_TABLE
         ) RETURN T_ORAVC_REPORT_TABLE IS
  l_result                              T_ORAVC_REPORT_TABLE;
  l_result_tmp                          T_ORAVC_REPORT_TABLE;
  l_result_record                       T_ORAVC_REPORT_RECORD;
  l_diff_result                         T_JAVA_REPORT_TABLE;
  l_diffclob                            CLOB;
  l_line_number_correction              NUMBER  := 0;
  l_obj_id                              NUMBER  := -1;
  l_action                              BOOLEAN;

  -- cursor to get delete changes for particular version
  CURSOR ver_changesDelete_cur (p_version_id VARCHAR2) IS
    SELECT doc_number
          ,line_number
          ,action
          ,line_text
      FROM TABLE(toSortJavaDiffTable)
     WHERE doc_number = p_version_id
       AND action = C_JAVAdeleted
     ORDER BY doc_number, line_number;

  -- cursor to get append changes for particular version
  CURSOR ver_changesAppend_cur (p_version_id VARCHAR2) IS
    SELECT doc_number
          ,line_number
          ,action
          ,line_text
      FROM TABLE(toSortJavaDiffTable)
     WHERE doc_number = p_version_id
       AND action = C_JAVAinserted
     ORDER BY doc_number, line_number;

BEGIN
  l_result := p_array;
  -- get differences of all tree for the star version to the end version
  l_diffclob    := oravc_get_2versions_diff(p_ovm_obj_parent, p_ovm_obj_version_id, p_version1_clob, p_version2_clob, 4);
  l_diff_result := getTableFromJavaDiff(l_diffclob);
  G_javaDiffTable := l_diff_result;

  -- for each version first apply delete changes to the array
  --FOR ver_changesDelete_rec IN ver_changesDelete_cur(versions_rec.doc_number) LOOP
  FOR ver_changesDelete_rec IN ver_changesDelete_cur(p_ovm_obj_parent) LOOP

    -- loop on the original first version code array
    l_line_number_correction := 0;
    FOR iArr IN 1..l_result.COUNT LOOP
      -- add line to tem array
      l_result_tmp(l_result_tmp.COUNT+1) := l_result(iArr);
      l_result_tmp(l_result_tmp.COUNT).obj_firstver_new_line_number := l_result_tmp(l_result_tmp.COUNT).obj_firstver_new_line_number + l_line_number_correction;

      IF ver_changesDelete_rec.line_number = l_result_tmp(iArr).obj_firstversion_line_number THEN
        -- start deleteion
        --l_result_tmp(l_result_tmp.COUNT).firstobj_id                  := NULL;
        l_result_tmp(l_result_tmp.COUNT).obj_firstversion_line_number := NULL;
        l_result_tmp(l_result_tmp.COUNT).obj_firstver_new_line_number := NULL;

        -- if line was deleted in newer version make line number correction
        IF l_result_tmp(l_result_tmp.COUNT).obj_line_added IS NOT NULL THEN
          -- this tells that line was added in one version and deleted in another version,
          -- so, to correctlly show this we need to create another line and mutate it
          l_result_tmp(l_result_tmp.COUNT).obj_line_mutated := C_MUTATED;
          l_result_tmp(l_result_tmp.COUNT+1) := l_result_tmp(l_result_tmp.COUNT);
          l_result_tmp(l_result_tmp.COUNT).obj_line_mutated := C_MUTATED;
        END IF;

        -- continue deleteion
        l_line_number_correction := l_line_number_correction - 1;
        l_result_tmp(l_result_tmp.COUNT).obj_line_deleted             := NVL(l_result_tmp(l_result_tmp.COUNT).obj_line_unchanged, l_result_tmp(l_result_tmp.COUNT).obj_line_added);
        l_result_tmp(l_result_tmp.COUNT).obj_line_unchanged           := NULL;
        l_result_tmp(l_result_tmp.COUNT).obj_line_added               := NULL;

        -- for modified lines always saving next version ID to see with which version made an action
        l_result_tmp(l_result_tmp.COUNT).obj_newversion_id            := p_ovm_obj_version_id;

        l_result_tmp(l_result_tmp.COUNT).obj_newversion_line_number   := NULL;
      END IF;
    END LOOP;

    -- reset arrays for the next version changes check
    l_result := l_result_tmp;
    l_result_tmp.DELETE;
  END LOOP;

  -- setup new line numbers
  FOR iArr IN 1..l_result.COUNT LOOP
    l_result(iArr).obj_firstversion_line_number := l_result(iArr).obj_firstver_new_line_number;
  END LOOP;

  -- for each version first apply append changes to the array
  --FOR ver_changesAppend_rec IN ver_changesAppend_cur(versions_rec.doc_number) LOOP
  FOR ver_changesAppend_rec IN ver_changesAppend_cur(p_ovm_obj_version_id) LOOP

    -- loop on the original first version code array
    l_line_number_correction := 0;
    FOR iArr IN 1..l_result.COUNT LOOP
      -- add line to tem array
      IF ver_changesAppend_rec.line_number = l_result(iArr).obj_firstversion_line_number THEN
        -- insert. make line number correction
        l_line_number_correction := l_line_number_correction + 1;

        -- add new record
        l_result_record.firstobj_id                  := NULL;
        l_result_record.obj_firstversion_id          := l_result(iArr).obj_firstversion_id;
        -- for modified lines always saving next version ID to see with which version made an action
        l_result_record.obj_newversion_id            := p_ovm_obj_version_id;

        l_result_record.obj_firstversion_line_number := l_result(iArr).obj_firstversion_line_number;
        l_result_record.obj_firstver_new_line_number := l_result(iArr).obj_firstversion_line_number;
        l_result_record.obj_line_unchanged           := NULL;
        l_result_record.obj_line_deleted             := NULL;
        l_result_record.obj_line_added               := ver_changesAppend_rec.line_text;
        l_result_record.obj_newversion_line_number   := ver_changesAppend_rec.line_number;
        l_result_record.line_order                   := l_result_tmp.COUNT+1;

        l_result_tmp(l_result_tmp.COUNT+1) := l_result_record;
      END IF;

      l_result_tmp(l_result_tmp.COUNT+1) := l_result(iArr);
      l_result_tmp(l_result_tmp.COUNT).obj_firstver_new_line_number := l_result_tmp(l_result_tmp.COUNT).obj_firstver_new_line_number + l_line_number_correction;
      l_result_tmp(l_result_tmp.COUNT).obj_firstversion_line_number := l_result_tmp(l_result_tmp.COUNT).obj_firstver_new_line_number;
      l_result_tmp(l_result_tmp.COUNT).line_order := l_result_tmp.COUNT;
    END LOOP;

    -- reset arrays for the next version changes check
    l_result := l_result_tmp;
    l_result_tmp.DELETE;
  END LOOP;

  RETURN l_result;

END diffArrayBuild;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_get_2versions_diff (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_version1_clob             CLOB
           ,p_version2_clob             CLOB
           ,p_display                   NUMBER := 0
         ) RETURN CLOB IS
  l_cresult                             CLOB;
  l_diffID                              NUMBER;

BEGIN
  setCurrentDiff('');

  javaDiff( NVL(p_version1_clob,'=')
           ,NVL(p_version2_clob, '=')
           ,p_display
           ,p_version_id1
           ,p_version_id2);

  RETURN getCurrentDiff;

END oravc_get_2versions_diff;

/*
<comments>
  <function>
    oravc_allINone_diffA_vc
  </function>

  <return>
    T_ORAVC_REPORT_TABLE
  </return>

  <description>
    Function returnes array for report about difference in two versions of packages
    all-in-one style
    It will take full branch of version between version1 and version2
    if therse are the different objects, and not are in the same brunch nothing will
    be done. Except the case when p_forceCompare flat = 1 (TRUE)
    In this case these versions will be compared to each other without branch fetch

    If nothig was done - then information message will be returned
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]
      [p_result_code]       [result of the function 0 - OK, 1 - error]        [NUMBER]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_allINone_diffA_vc (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_result_code          OUT  NUMBER
         ) RETURN T_ORAVC_REPORT_TABLE
  IS
  l_result                              T_ORAVC_REPORT_TABLE;
  l_result_record                       T_ORAVC_REPORT_RECORD;
  l_firstobj_id                         NUMBER  := -1;
  l_branchIsCorrect                     BOOLEAN := FALSE;
  l_forceCompare                        NUMBER := 0;

  l_check_count1                        NUMBER := 0;
  l_check_count2                        NUMBER := 0;

  CURSOR firstversion_lines_cur(cp_version_id NUMBER) IS
    SELECT line_id
          ,line_text
      FROM TABLE(oravc_engine.oravc_get_obj_source_lines(cp_version_id))
     ORDER BY line_id;

  CURSOR all_versions_cur (cp_version_start NUMBER, cp_version_end NUMBER)
  IS
    SELECT ovm_obj_parent
          ,ovm_obj_version_id
      FROM oravc_obj_version
     WHERE ovm_obj_version_id <= cp_version_end
       -- TO BE SHURE THAT VERSION OF THE SAME BRUNCH
       AND cp_version_start IN (
              SELECT ovm_obj_version_id
                FROM oravc_obj_version
             CONNECT BY PRIOR ovm_obj_parent = ovm_obj_version_id
               START WITH ovm_obj_version_id = cp_version_end
          )
   CONNECT BY PRIOR ovm_obj_version_id = ovm_obj_parent
     START WITH ovm_obj_parent = cp_version_start
     ORDER BY LEVEL ASC;

  CURSOR force_versions_cur (cp_version_start NUMBER, cp_version_end NUMBER)
    IS
    SELECT * from dual;

BEGIN
  p_result_code := 0;
  l_forceCompare := p_forceCompare;
  -- first need to check are versions ID's exists, otherwise - nothing to compare
  SELECT count(*)
    INTO l_check_count1
    FROM oravc_obj_version
   WHERE ovm_obj_version_id = p_version_id1;

  SELECT count(*)
    INTO l_check_count2
    FROM oravc_obj_version
   WHERE ovm_obj_version_id = p_version_id2;

  IF l_check_count1 = 0 OR l_check_count2 = 0 THEN
    -- one of ID's deos not exist
    -- if nothing was processed then in report put error information
    l_result.DELETE;

    IF l_check_count1 = 0 THEN
      l_result_record.firstobj_id                  := NULL;
      l_result_record.obj_firstversion_id          := NULL;
      l_result_record.obj_newversion_id            := NULL;
      l_result_record.obj_firstversion_line_number := NULL;
      l_result_record.obj_firstver_new_line_number := NULL;
      l_result_record.obj_line_unchanged           := 'Object version ' || p_version_id1 || ' does not exist ';
      l_result_record.obj_line_deleted             := NULL;
      l_result_record.obj_line_added               := NULL;
      l_result_record.obj_newversion_line_number   := NULL;
      l_result_record.line_order                   := NULL;
      l_result(l_result.COUNT+1) := l_result_record;
    END IF;

    IF l_check_count2 = 0 THEN
      l_result_record.firstobj_id                  := NULL;
      l_result_record.obj_firstversion_id          := NULL;
      l_result_record.obj_newversion_id            := NULL;
      l_result_record.obj_firstversion_line_number := NULL;
      l_result_record.obj_firstver_new_line_number := NULL;
      l_result_record.obj_line_unchanged           := 'Object version ' || p_version_id2 || ' does not exist ';
      l_result_record.obj_line_deleted             := NULL;
      l_result_record.obj_line_added               := NULL;
      l_result_record.obj_newversion_line_number   := NULL;
      l_result_record.line_order                   := NULL;
      l_result(l_result.COUNT+1) := l_result_record;
    END IF;

    l_result_record.firstobj_id                  := NULL;
    l_result_record.obj_firstversion_id          := NULL;
    l_result_record.obj_newversion_id            := NULL;
    l_result_record.obj_firstversion_line_number := NULL;
    l_result_record.obj_firstver_new_line_number := NULL;
    l_result_record.obj_line_unchanged           := 'Nothing to compare';
    l_result_record.obj_line_deleted             := NULL;
    l_result_record.obj_line_added               := NULL;
    l_result_record.obj_newversion_line_number   := NULL;
    l_result_record.line_order                   := NULL;
    l_result(l_result.COUNT+1) := l_result_record;

    p_result_code := 1;
    return l_result;
  END IF;

  IF p_version_id1 = p_version_id2 THEN
    -- if task is to compare two similar ID's then force comparison
    l_forceCompare := 1;
  END IF;

  -- first load all lines from the first version.
  FOR firstversion_lines_rec IN firstversion_lines_cur(p_version_id1) LOOP
    -- writing record from the firstversion
    l_firstobj_id := getVCObjectIDByVersion(p_version_id1);
    l_result_record.firstobj_id                  := firstversion_lines_rec.line_id;
    l_result_record.obj_firstversion_id          := p_version_id1;
    l_result_record.obj_newversion_id            := p_version_id2;
    l_result_record.obj_firstversion_line_number := firstversion_lines_rec.line_id;
    l_result_record.obj_firstver_new_line_number := firstversion_lines_rec.line_id;
    l_result_record.obj_line_unchanged           := firstversion_lines_rec.line_text;
    l_result_record.obj_line_deleted             := NULL;
    l_result_record.obj_line_added               := NULL;
    l_result_record.obj_newversion_line_number   := NULL;
    l_result_record.line_order                   := firstversion_lines_rec.line_id;

    l_result(l_result.COUNT+1) := l_result_record;
  END LOOP;

  l_branchIsCorrect := FALSE;
  IF l_forceCompare <> 1 THEN
    -- if force Compare not applyed

    FOR all_versions_rec IN all_versions_cur(p_version_id1, p_version_id2) LOOP
      l_branchIsCorrect := TRUE;
      l_result := diffArrayBuild( all_versions_rec.ovm_obj_parent
                                 ,all_versions_rec.ovm_obj_version_id
                                 ,oravc_getVCVersionCode(all_versions_rec.ovm_obj_parent)
                                 ,oravc_getVCVersionCode(all_versions_rec.ovm_obj_version_id)
                                 ,l_result
                                );
    END LOOP;
  END IF;

  IF l_branchIsCorrect = FALSE AND l_forceCompare = 1 THEN
    -- this actually means that versions are not on the same branch
    -- and FORCE flag to compare explisit objects
    -- (possible different objects)
    l_branchIsCorrect := TRUE;
    --l_result := diffArrayBuild(p_version_id1, p_version_id2, l_result);
    l_result := diffArrayBuild( p_version_id1
                               ,p_version_id2
                               ,oravc_getVCVersionCode(p_version_id1)
                               ,oravc_getVCVersionCode(p_version_id2)
                               ,l_result
                              );
  END IF;

  -- if branch true - e.t. means that something was compared
  IF l_branchIsCorrect THEN
    -- now for all lines setup correct line order and put back original ID's for lines which were not changed
    FOR iArr IN 1..l_result.COUNT LOOP
      l_result(iArr).obj_firstversion_line_number := l_result(iArr).firstobj_id;
      -- if line was not changed - then new_line number must be equal to original line number
      IF l_result(iArr).obj_line_unchanged IS NOT NULL THEN
        l_result(iArr).obj_newversion_line_number := l_result(iArr).obj_firstver_new_line_number;
      END IF;

      l_result(iArr).firstobj_id := l_firstobj_id;
    END LOOP;

  ELSE
    -- if nothing was processed then in report put error information
    l_result.DELETE;

    l_result_record.firstobj_id                  := NULL;
    l_result_record.obj_firstversion_id          := NULL;
    l_result_record.obj_newversion_id            := NULL;
    l_result_record.obj_firstversion_line_number := NULL;
    l_result_record.obj_firstver_new_line_number := NULL;
    l_result_record.obj_line_unchanged           := 'Versions ' || p_version_id1 || ' and ' || p_version_id2 || ' ';
    l_result_record.obj_line_deleted             := NULL;
    l_result_record.obj_line_added               := NULL;
    l_result_record.obj_newversion_line_number   := NULL;
    l_result_record.line_order                   := NULL;
    l_result(l_result.COUNT+1) := l_result_record;

    l_result_record.firstobj_id                  := NULL;
    l_result_record.obj_firstversion_id          := NULL;
    l_result_record.obj_newversion_id            := NULL;
    l_result_record.obj_firstversion_line_number := NULL;
    l_result_record.obj_firstver_new_line_number := NULL;
    l_result_record.obj_line_unchanged           := 'are not at the same branch. Probablly these are the different objects ';
    l_result_record.obj_line_deleted             := NULL;
    l_result_record.obj_line_added               := NULL;
    l_result_record.obj_newversion_line_number   := NULL;
    l_result_record.line_order                   := NULL;
    l_result(l_result.COUNT+1) := l_result_record;

    l_result_record.firstobj_id                  := NULL;
    l_result_record.obj_firstversion_id          := NULL;
    l_result_record.obj_newversion_id            := NULL;
    l_result_record.obj_firstversion_line_number := NULL;
    l_result_record.obj_firstver_new_line_number := NULL;
    l_result_record.obj_line_unchanged           := 'or you are trying to compare newer version with older one.';
    l_result_record.obj_line_deleted             := NULL;
    l_result_record.obj_line_added               := NULL;
    l_result_record.obj_newversion_line_number   := NULL;
    l_result_record.line_order                   := NULL;
    l_result(l_result.COUNT+1) := l_result_record;

    l_result_record.firstobj_id                  := NULL;
    l_result_record.obj_firstversion_id          := NULL;
    l_result_record.obj_newversion_id            := NULL;
    l_result_record.obj_firstversion_line_number := NULL;
    l_result_record.obj_firstver_new_line_number := NULL;
    l_result_record.obj_line_unchanged           := 'To compare these versions call API with "compare force flag" set to TRUE';
    l_result_record.obj_line_deleted             := NULL;
    l_result_record.obj_line_added               := NULL;
    l_result_record.obj_newversion_line_number   := NULL;
    l_result_record.line_order                   := NULL;
    l_result(l_result.COUNT+1) := l_result_record;

    l_result_record.firstobj_id                  := NULL;
    l_result_record.obj_firstversion_id          := NULL;
    l_result_record.obj_newversion_id            := NULL;
    l_result_record.obj_firstversion_line_number := NULL;
    l_result_record.obj_firstver_new_line_number := NULL;
    l_result_record.obj_line_unchanged           := '(p_forceCompare = 1)';
    l_result_record.obj_line_deleted             := NULL;
    l_result_record.obj_line_added               := NULL;
    l_result_record.obj_newversion_line_number   := NULL;
    l_result_record.line_order                   := NULL;
    l_result(l_result.COUNT+1) := l_result_record;

    p_result_code := 1;
  END IF;

  RETURN l_result;
END oravc_allINone_diffA_vc;

/*
<comments>
  <procedure>
    oravc_clean_old_versions
  </procedure>

  <description>
    This procedure deletes old versions of the objects from the date lower than {sysdate - p_setcleanvers_olderthen}
    If p_setcleanvers_olderthen parameter is not null then
      cleans old data lower {sysdate - p_setcleanvers_olderthen_day}
      but never deletes last version of the object
    by default p_setcleanvers_olderthen_day is null
  </description>

  <parameters>
    <IN>
      [p_setcleanvers_olderthen]    [number of days which indicates old version]      [NUMBER]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
PROCEDURE oravc_clean_old_versions(
            p_setcleanvers_olderthen_day                NUMBER
          ) AS
  PRAGMA AUTONOMOUS_TRANSACTION;

  l_function_name                             VARCHAR2(64) := 'oravc_clean_old_versions';

BEGIN
  IF p_setcleanvers_olderthen_day IS NOT NULL THEN
    DELETE FROM ORAVC_OBJ_VERSION
          WHERE OVM_OBJ_VERSION_DATE < SYSDATE - p_setcleanvers_olderthen_day
            AND (OVM_OBJ_VC_ID, OVM_OBJ_VERSION_ID)
                    NOT IN (
                              SELECT OVM_OBJ_VC_ID
                                    ,MAX(OVM_OBJ_VERSION_ID) AS MAX_VERSION_ID
                                FROM oravc_obj_version
                               GROUP BY OVM_OBJ_VC_ID
                           );
    COMMIT;

    add2log(
       p_message_type    => C_EVENT
      ,p_message_text    => 'Old versions cleanup was done. ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );

    -- now update deleted parent versions ID's
    UPDATE ORAVC_OBJ_VERSION
       SET ovm_obj_parent = NULL
     WHERE ovm_obj_parent IS NOT NULL
       AND NOT EXISTS (SELECT vview.ovm_obj_version_id
                         FROM oravc_obj_version vview
                        WHERE vview.ovm_obj_version_id = ORAVC_OBJ_VERSION.ovm_obj_parent
                      );
    COMMIT;

    add2log(
       p_message_type    => C_EVENT
      ,p_message_text    => 'Invalidated parent record updated.' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );


  END IF;
END oravc_clean_old_versions;

/*
<comments>
  <procedure>
    oravc_run_vc
  </procedure>

  <description>
    This procedure is a main version control check.
    It is used for identifying if any new version is need. Used in the SCHEDULER job.
    It checks if there were any new objects created for the registered users and objects types.
    Then checkes if there were any changes in the already existing objects in Version Controll.
    Checks are made by the comparison of the source codes.
  </description>

  <parameters>
    <IN>
      [p_objowner]          [oracle object owner]                         [VARCHAR2]  [NULL]
      [p_objname]           [oracle obkect name]                          [VARCHAR2]  [NULL]
      [p_objtype]           [object type: 'PACKAGE', 'PACKAGE BODY' etc]  [VARCHAR2]  [NULL]
      [p_obj_versioninfo]   [if there is some spetial info about version] [VARCHAR2]  [NULL]

    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
    [1.1] [25-10-2012] [Andrey Tsyganov] [bug fix] []
  </version>

</comments>
*/
PROCEDURE oravc_run_vc(
             p_owner            VARCHAR2
            ,p_objname          VARCHAR2
            ,p_objtype          VARCHAR2
            ,p_obj_versioninfo  VARCHAR2
          ) AS

  l_function_name               VARCHAR2(64) := 'oravc_run_vc';
  l_obj_source                  CLOB;
  l_ora_obj_source              CLOB;
  l_tmp_source                  CLOB;
  l_ora_obj_source_lines        T_CLOB_TABLE;
  l_obj_vc_id                   oravc_obj.vco_obj_vc_id%TYPE;
  l_obj_version_id              oravc_obj_version.ovm_obj_version_id%TYPE;
  l_add_version                 BOOLEAN;
  l_obj_lastversion             NUMBER;
  l_last_ddl_time               DATE;

  CURSOR new_orausr_obj_cur(
            cp_owner            VARCHAR2
           ,cp_objname          VARCHAR2
           ,cp_objtype          VARCHAR2
         )
  IS
    SELECT owner       AS owner
          ,object_name AS object_name
          ,object_type AS object_type
      FROM dba_objects
     WHERE owner IN (SELECT vc_ora_user
                       FROM oravc_usr2monitor
                    )
       AND object_type IN (SELECT vc_ora_objtype
                             FROM oravc_objtype2monitor
                          )
       AND (cp_owner IS NULL OR owner = cp_owner)
       AND (cp_objname IS NULL OR object_name = cp_objname)
       AND (cp_objtype IS NULL OR object_type = cp_objtype)
       AND object_name NOT LIKE 'SYS_PLSQL%'
     MINUS

    SELECT vco_obj_owner, vco_obj_name, vco_obj_type
      FROM oravc_obj
     WHERE (cp_owner IS NULL OR vco_obj_owner = cp_owner)
       AND (cp_objname IS NULL OR vco_obj_name = cp_objname)
       AND (cp_objtype IS NULL OR vco_obj_type = cp_objtype);

  CURSOR old_orausr_obj_cur(
            cp_owner            VARCHAR2
           ,cp_objname          VARCHAR2
           ,cp_objtype          VARCHAR2
         )
  IS
    SELECT vco_obj_owner
          ,vco_obj_name
          ,vco_obj_type
          ,vco_obj_vc_id
      FROM oravc_obj
     WHERE (cp_owner IS NULL OR vco_obj_owner = cp_owner)
       AND (cp_objname IS NULL OR vco_obj_name = cp_objname)
       AND (cp_objtype IS NULL OR vco_obj_type = cp_objtype);

BEGIN
  FOR new_orausr_obj_rec IN new_orausr_obj_cur(p_owner, p_objname, p_objtype)  LOOP
    l_obj_vc_id := oravc_oravc_obj_insert(
                      p_obj_owner => new_orausr_obj_rec.owner
                     ,p_obj_name  => new_orausr_obj_rec.object_name
                     ,p_obj_type  => new_orausr_obj_rec.object_type
                   );
  END LOOP;

  FOR old_orausr_obj_rec IN old_orausr_obj_cur(p_owner, p_objname, p_objtype)  LOOP
    BEGIN
      /*
      add2log(
         p_message_type    => C_EVENT
        ,p_message_text    => 'Processing ' || old_orausr_obj_rec.vco_obj_type || ' ' || old_orausr_obj_rec.vco_obj_owner  || '.' || old_orausr_obj_rec.vco_obj_name || ' ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
        ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
        );
      */

      oravc_get_obj_source(
         p_objowner       => old_orausr_obj_rec.vco_obj_owner
        ,p_objname        => old_orausr_obj_rec.vco_obj_name
        ,p_objtype        => old_orausr_obj_rec.vco_obj_type
        ,p_res_source     => l_ora_obj_source
      );
      --l_ora_obj_source_lines := oravc_getVCVersionCodeArray(l_ora_obj_source);

      l_add_version := FALSE;

      BEGIN
        SELECT max(ovm_obj_version_id)
          INTO l_obj_lastversion
          FROM oravc_obj_version
        WHERE ovm_obj_vc_id = old_orausr_obj_rec.vco_obj_vc_id;

        SELECT ovm_obj_source
          INTO l_obj_source
          FROM oravc_obj_version
        WHERE ovm_obj_version_id = l_obj_lastversion;

        /*
        IF l_obj_source IS NOT NULL THEN
          l_obj_source := REPLACE(l_obj_source, C_ADDED_CHAR);
          IF SUBSTR(l_ora_obj_source, LENGTH(l_ora_obj_source), 1) <> chr(10) THEN
            l_obj_source := SUBSTR(l_obj_source, 1, LENGTH(l_obj_source) -1);
          END IF;
        ELSE
          l_obj_source := '=';
        END IF;
        */
        l_tmp_source := appendSourceWithVCChar(l_ora_obj_source);
        IF l_obj_source <> l_tmp_source THEN
          l_add_version := TRUE;
        END IF;

      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_add_version := TRUE;
      END;

      IF l_add_version THEN
        BEGIN
          SELECT LAST_DDL_TIME
            INTO l_last_ddl_time
            FROM dba_objects
           WHERE owner = old_orausr_obj_rec.vco_obj_owner
             AND object_name  = old_orausr_obj_rec.vco_obj_name
             AND object_type  = old_orausr_obj_rec.vco_obj_type;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            l_last_ddl_time := NULL;
        END;

        l_obj_version_id := oravc_oravc_version_insert(
                               p_obj_vc_id             => old_orausr_obj_rec.vco_obj_vc_id
                             ,p_obj_version_date      => SYSDATE
                             ,p_obj_source            => l_ora_obj_source
                             ,p_obj_parent            => l_obj_lastversion
                             ,p_obj_ver_ddl_creattime => l_last_ddl_time
                             ,p_obj_versioninfo       => p_obj_versioninfo
                           );

      END IF;

      /*
      add2log(
         p_message_type    => C_EVENT
        ,p_message_text    => 'Complete synchronization of the object ' || old_orausr_obj_rec.vco_obj_type || ' ' || old_orausr_obj_rec.vco_obj_owner  || '.' || old_orausr_obj_rec.vco_obj_name || ' ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
        ,p_message_place   => C_PACKAGE_NAME || '.' || l_function_name
        );
      */

    EXCEPTION
      WHEN OTHERS THEN
        add2log(
           p_message_type    => C_ERROR
          ,p_message_text    => 'ERROR IN ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
              || SQLCODE || chr(10) || l_function_name    || chr(10)
              || 'called with: ' || chr(10)
              || ',p_owner => '                       || p_owner                      || chr(10)
              || ',p_objtype => '                     || p_objtype                    || chr(10)
              || ',p_objname => '                     || p_objname                    || chr(10)
              || ' error on object => '|| old_orausr_obj_rec.vco_obj_owner || '=>' || old_orausr_obj_rec.vco_obj_name || '=> ' || old_orausr_obj_rec.vco_obj_type
              || chr(10)
              || SQLERRM
          ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
        );

    END;
  END LOOP;
END oravc_run_vc;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE oravc_main_vc(
             p_owner                          VARCHAR2 := NULL
            ,p_objname                        VARCHAR2 := NULL
            ,p_objtype                        VARCHAR2 := NULL
            ,p_setcleanvers_olderthen_day     NUMBER   := NULL
            ,p_obj_versioninfo                VARCHAR2 := NULL
          ) AS
  PRAGMA AUTONOMOUS_TRANSACTION;

  l_function_name                             VARCHAR2(64) := 'oravc_main_vc';
  l_user_to_monitor_count                     NUMBER;
  l_obj_vc_id                                 NUMBER;
  l_obj_version_id                            NUMBER;
  l_versionscount                             NUMBER;
  l_procesedTRG_events                        T_NUMBER_TABLE;
  l_source                                    CLOB;

  CURSOR triggered_events_cur IS
    SELECT  oot_record_id
           ,oot_eventtime
           ,oot_objowner
           ,oot_objname
           ,oot_objtype
           ,oot_objversioninfo
           ,oot_objsource
      FROM oravc_obj_trgchanges;

  CURSOR latesVCObjVer_cur(cp_objowner VARCHAR2, cp_objtype VARCHAR2, cp_objname VARCHAR2) IS
    SELECT ovm_obj_vc_id, ovm_obj_version_id, ovm_obj_source
      FROM oravc_obj_version
     WHERE ovm_obj_version_id = (SELECT max(ovm_obj_version_id) as vid
                                   FROM oravc_obj_version
                                       ,oravc_obj
                                  WHERE oravc_obj.vco_obj_vc_id = oravc_obj_version.ovm_obj_vc_id
                                    AND oravc_obj.vco_obj_owner = cp_objowner
                                    AND oravc_obj.vco_obj_type  = cp_objtype
                                    AND oravc_obj.vco_obj_name  = cp_objname
                                );
BEGIN
  SELECT count(*)
    INTO l_user_to_monitor_count
    FROM oravc_usr2monitor;

  IF l_user_to_monitor_count > 0 THEN

    add2log(
       p_message_type    => C_EVENT
      ,p_message_text    => 'Processing events saved by oravc_trg ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      ,p_message_place   => C_PACKAGE_NAME || '.' || l_function_name
      );

    FOR triggered_events_rec IN triggered_events_cur LOOP
      --check if object is new to the VC
      SELECT count(*)
        INTO l_versionscount
        FROM oravc_obj
       WHERE oravc_obj.vco_obj_owner = triggered_events_rec.oot_objowner
         AND oravc_obj.vco_obj_type  = triggered_events_rec.oot_objtype
         AND oravc_obj.vco_obj_name  = triggered_events_rec.oot_objname;

      IF l_versionscount = 0 THEN
        l_obj_vc_id := oravc_oravc_obj_insert(
                          p_obj_owner => triggered_events_rec.oot_objowner
                         ,p_obj_name  => triggered_events_rec.oot_objname
                         ,p_obj_type  => triggered_events_rec.oot_objtype
                        );

        l_obj_version_id := oravc_oravc_version_insert(
                               p_obj_vc_id             => l_obj_vc_id
                              ,p_obj_version_date      => SYSDATE
                              ,p_obj_source            => triggered_events_rec.oot_objsource
                              ,p_obj_parent            => NULL
                              ,p_obj_ver_ddl_creattime => triggered_events_rec.oot_eventtime
                              ,p_obj_versioninfo       => triggered_events_rec.oot_objversioninfo
                            );
      END IF;

      l_source := appendSourceWithVCChar(triggered_events_rec.oot_objsource);
      FOR iSource IN latesVCObjVer_cur(triggered_events_rec.oot_objowner, triggered_events_rec.oot_objtype, triggered_events_rec.oot_objname) LOOP
        IF l_source <> iSource.ovm_obj_source THEN
          l_obj_version_id := oravc_oravc_version_insert(
                                 p_obj_vc_id             => iSource.ovm_obj_vc_id
                                ,p_obj_version_date      => SYSDATE
                                ,p_obj_source            => triggered_events_rec.oot_objsource
                                ,p_obj_parent            => iSource.ovm_obj_version_id
                                ,p_obj_ver_ddl_creattime => triggered_events_rec.oot_eventtime
                                ,p_obj_versioninfo       => triggered_events_rec.oot_objversioninfo
                              );
        END IF;
      END LOOP;

      l_procesedTRG_events(l_procesedTRG_events.COUNT+1) := triggered_events_rec.oot_record_id;
    END LOOP;

    -- clean processed events
    FOR iEvent IN 1..l_procesedTRG_events.COUNT LOOP
      DELETE FROM oravc_obj_trgchanges WHERE oot_record_id = l_procesedTRG_events(iEvent);
    END LOOP;

    COMMIT;

    add2log(
       p_message_type    => C_EVENT
      ,p_message_text    => 'oravc_trg events were processed ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );

    add2log(
       p_message_type    => C_EVENT
      ,p_message_text    => 'Processing Version Controll content synchronization.' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );

    oravc_run_vc(
       p_owner           => p_owner
      ,p_objname         => p_objname
      ,p_objtype         => p_objtype
      ,p_obj_versioninfo => p_obj_versioninfo
    );

    add2log(
       p_message_type    => C_EVENT
      ,p_message_text    => 'Version controll content synchronization was successful. ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );



    oravc_clean_old_versions(p_setcleanvers_olderthen_day);
  ELSE
    add2log(
       p_message_type    => C_ERROR
      ,p_message_text    => 'No users to monitor. Add users to oravc_usr2monitor table using: execute oravc_engine.oravc_add_user2monitor(<username>);' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    add2log(
       p_message_type    => C_ERROR
      ,p_message_text    => 'ERROR IN ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      || SQLCODE || chr(10) || l_function_name    || chr(10)
      || '    ,p_owner => '                       || p_owner                      || chr(10)
      || '    ,p_objtype => '                     || p_objtype                    || chr(10)
      || '    ,p_objname => '                     || p_objname                    || chr(10)
      || SQLERRM
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );

END oravc_main_vc;

/*
<comments>
  <function>
    oravc_allINone_diffA_cl
  </function>

  <return>
    T_ORAVC_REPORT_TABLE
  </return>

  <description>
    Function returnes array for report about difference in two any CLOBs
    all-in-one style
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]
      [p_version2_clob]     [clob 1 for direct clob difference check]         [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_allINone_diffA_cl (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_version1_clob             CLOB
           ,p_version2_clob             CLOB
         ) RETURN T_ORAVC_REPORT_TABLE
  IS
  l_result                              T_ORAVC_REPORT_TABLE;
  l_result_record                       T_ORAVC_REPORT_RECORD;
  p_version1_source                     T_CLOB_TABLE;
  l_firstobj_id                         NUMBER  := -1;
  l_branchIsCorrect                     BOOLEAN := FALSE;
  l_forceCompare                        NUMBER := 0;

BEGIN
  p_version1_source := oravc_getVCVersionCodeArray(p_version1_clob);
  -- first load all lines from the first version.
  FOR i IN 1..p_version1_source.COUNT LOOP
    -- writing record from the firstversion
    l_firstobj_id := -1;
    l_result_record.firstobj_id                  := i;
    l_result_record.obj_firstversion_id          := p_version_id1;
    l_result_record.obj_newversion_id            := p_version_id2;
    l_result_record.obj_firstversion_line_number := i;
    l_result_record.obj_firstver_new_line_number := i;
    l_result_record.obj_line_unchanged           := p_version1_source(i);
    l_result_record.obj_line_deleted             := NULL;
    l_result_record.obj_line_added               := NULL;
    l_result_record.obj_newversion_line_number   := NULL;
    l_result_record.line_order                   := i;

    l_result(l_result.COUNT+1)            := l_result_record;
  END LOOP;

  l_result := diffArrayBuild(p_version_id1, p_version_id2, p_version1_clob, p_version2_clob, l_result);

  -- now for all lines setup correct line order and put back original ID's for lines which were not changed
  FOR iArr IN 1..l_result.COUNT LOOP
    l_result(iArr).obj_firstversion_line_number := l_result(iArr).firstobj_id;
    -- if line was not changed - then new_line number must be equal to original line number
    IF l_result(iArr).obj_line_unchanged IS NOT NULL THEN
      l_result(iArr).obj_newversion_line_number := l_result(iArr).obj_firstver_new_line_number;
    END IF;

    l_result(iArr).firstobj_id := l_firstobj_id;
  END LOOP;

  RETURN l_result;
END oravc_allINone_diffA_cl;

/*
<comments>
  <function>
    oravc_allINone_diffA
  </function>

  <return>
    T_ORAVC_REPORT_TABLE
  </return>

  <description>
    Function returnes array for report about difference of CLOBS or Versions
    all-in-one style

    IF CLOB1 and CLOB2 are NULL (default) then Versions Controll objects will be checked by
    there ID's p_version_id1, p_version_id2
    Parameters p_version1_clob, p_version2_clob optional in case direct comparison of any text needed
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]    [0   ]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_version2_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_result_code]       [result of the operation 0 - OK, 1 - error]       [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_allINone_diffA (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
           ,p_result_code           OUT NUMBER
         ) RETURN T_ORAVC_REPORT_TABLE IS
  l_result_table                        T_ORAVC_REPORT_TABLE;

BEGIN
  IF (p_version1_clob IS NULL OR p_version2_clob IS NULL) THEN
    -- this is normal Version Controll objects compare
    l_result_table := oravc_allINone_diffA_vc (p_version_id1, p_version_id2, p_forceCompare, p_result_code);
  ELSE
    -- this is force comparison for two CLOBs which are not part of  the Version Controll
    --  || C_ADDED_CHAR || ' ' || chr(10) added to be sure that there is at least one similarity at the end of the clobs
    l_result_table := oravc_allINone_diffA_cl (p_version_id1
                                        ,p_version_id2
                                        ,p_version1_clob || chr(10) || C_ADDED_CHAR
                                        ,p_version2_clob || chr(10) || C_ADDED_CHAR
                                        );
  END IF;

  RETURN l_result_table;
END oravc_allINone_diffA;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_allINone_diff (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN T_ORAVC_REPORT_ARRAY  PIPELINED
  IS
  l_rarray                              T_ORAVC_REPORT_TABLE;
  l_result_code                         NUMBER;

BEGIN
  l_rarray := oravc_allINone_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob, l_result_code);

  FOR i IN 1..l_rarray.COUNT LOOP
    PIPE ROW (l_rarray(i));
  END LOOP;

  RETURN;
END oravc_allINone_diff;

/*
<comments>
  <function>
    oravc_sideBYside_diffA
  </function>

  <return>
    T_ORAVC_REPORT2_TABLE
  </return>

  <description>
    Function returnes array for report about difference of CLOBS or Versions
    This is syde-by-sied style of report
  </description>

  <parameters>
    <IN>
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_obj_version_id]    [version controll obeject version id]             [NUMBER]
      [p_forceCompare]      [forces compation of the version1 and version2]   [NUMBER]
      [p_version1_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
      [p_version2_clob]     [clob 1 for direct clob difference check]         [CLOB]      [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_sideBYside_diffA (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN T_ORAVC_REPORT2_TABLE
  IS
  l_rarray                              T_ORAVC_REPORT_TABLE;
  l_r2record                            T_ORAVC_REPORT2_RECORD;
  l_result                              T_ORAVC_REPORT2_TABLE;
  l_version2_source_array               T_CLOB_TABLE;
  l_result_code                         NUMBER;

  -- get lines from version structures, or from CLOB
  FUNCTION lf_GetObjectID(
             p_version_id               NUMBER
          ) RETURN NUMBER IS
    l_result                            NUMBER;
  BEGIN
    IF (p_version1_clob IS NULL AND p_version2_clob IS NULL) THEN
      SELECT DISTINCT ovm_obj_vc_id
        INTO l_result
        FROM oravc_obj_version
      WHERE ovm_obj_version_id = p_version_id;

      RETURN l_result;
    ELSE
      RETURN -1;
    END IF;
  END lf_GetObjectID;

BEGIN
  l_rarray := oravc_allINone_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob, l_result_code);
  l_version2_source_array := oravc_getVCVersionCodeArray(p_version2_clob);

  FOR i IN 1..l_rarray.COUNT LOOP
    l_r2record.line_order               := l_rarray(i).line_order;
    l_r2record.firstobj_id              := l_rarray(i).firstobj_id;
    l_r2record.obj_firstversion_id      := l_rarray(i).obj_firstversion_id;

    IF l_rarray(i).obj_newversion_id IS NOT NULL THEN
      l_r2record.newobj_id := lf_GetObjectID(l_rarray(i).obj_newversion_id);
    END IF;

    l_r2record.obj_newversion_id             := l_rarray(i).obj_newversion_id;
    l_r2record.unchanged                     := CASE
                                                  WHEN l_rarray(i).obj_line_unchanged IS NULL THEN
                                                    NULL
                                                  ELSE
                                                    C_UNCHANGED
                                                END;
    l_r2record.deleted                       := CASE
                                                  WHEN l_rarray(i).obj_line_deleted IS NULL THEN
                                                    NULL
                                                  ELSE
                                                    C_DELETED
                                               END;
    l_r2record.added                         := CASE
                                                  WHEN l_rarray(i).obj_line_added IS NULL THEN
                                                      NULL
                                                  ELSE
                                                    C_ADDED
                                                END;
    l_r2record.mutated                       := l_rarray(i).obj_line_mutated;
    l_r2record.obj_firstversion_line_number  := l_rarray(i).obj_firstversion_line_number;
    l_r2record.obj_firstver_new_line_number  := l_rarray(i).obj_firstver_new_line_number;
    l_r2record.obj_firstversion_line_text    := CASE
                                                  WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
                                                    l_rarray(i).obj_line_unchanged
                                                  WHEN l_rarray(i).obj_line_deleted IS NOT NULL THEN
                                                    l_rarray(i).obj_line_deleted
                                                  WHEN l_rarray(i).obj_line_added IS NOT NULL THEN
                                                    ''
                                                  ELSE
                                                    NULL
                                                END;
    l_r2record.obj_newversion_line_number    := l_rarray(i).obj_newversion_line_number;

    IF l_rarray(i).obj_newversion_line_number IS NOT NULL THEN
      BEGIN
        IF p_version1_clob IS NULL AND p_version2_clob IS NULL THEN
          SELECT ovl_line_source
            INTO l_r2record.obj_newversion_line_text
            FROM oravc_obj_version_lines
           WHERE ovl_line_id = l_rarray(i).obj_newversion_line_number
             AND ovl_version_id = l_rarray(i).obj_newversion_id;
        ELSE
          -- if we have comparison of two CLOBS
          l_r2record.obj_newversion_line_text := l_version2_source_array(l_rarray(i).obj_newversion_line_number);
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_r2record.obj_newversion_line_text := '=>ERROR! NO DATA FOUND looks like a bug somewhere.... : ' || l_rarray(i).obj_newversion_id || ':' || l_rarray(i).obj_newversion_line_number;
        END;
    ELSE
      l_r2record.obj_newversion_line_text := NULL; -- tupo to check
    END IF;

    l_result(l_result.COUNT + 1) := l_r2record;
  END LOOP;

  RETURN l_result;
END oravc_sideBYside_diffA;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_sideBYside_diff (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN T_ORAVC_REPORT2_ARRAY PIPELINED
  IS
  l_r2array                              T_ORAVC_REPORT2_TABLE;

BEGIN
  l_r2array := oravc_sideBYside_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob);

  FOR i IN 1..l_r2array.COUNT LOOP
    PIPE ROW (l_r2array(i));
  END LOOP;

  RETURN;
END oravc_sideBYside_diff;


/*
<comments>
  <function>
    build_allINone_clob
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes CLOB with formated versions difference report in simple text
    all-in-one style
  </description>

  <parameters>
    <IN>
      [p_array]    [array with versions diffence result]    [T_ORAVC_REPORT_TABLE]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>
</comments>
*/
FUNCTION build_allINone_clob (
           p_array                      T_ORAVC_REPORT_TABLE
         ) RETURN CLOB IS

  l_rarray                              T_ORAVC_REPORT_TABLE;
  l_clob                                CLOB;
  l_max_line_length                     NUMBER := -1;
  l_max_id_length                       NUMBER := -1;
  l_temp_line                           CLOB := '';
  l_temp_head                           VARCHAR2(50) := '';

BEGIN
  l_rarray := p_array;
  dbms_lob.createtemporary(l_clob, TRUE);
  FOR i IN 1..l_rarray.COUNT LOOP
    l_temp_line := CASE
                      WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
                        l_rarray(i).obj_line_unchanged
                      WHEN l_rarray(i).obj_line_deleted IS NOT NULL THEN
                        l_rarray(i).obj_line_deleted
                      WHEN l_rarray(i).obj_line_added IS NOT NULL THEN
                        l_rarray(i).obj_line_added
                      ELSE
                        NULL
                   END;

    IF LENGTH(l_temp_line) > l_max_line_length THEN
      l_max_line_length := LENGTH(l_temp_line);
    END IF;

    IF LENGTH(NVL(l_rarray(i).obj_firstversion_id, '')) > l_max_id_length THEN
      l_max_id_length := LENGTH(NVL(l_rarray(i).obj_firstversion_id, ''));
    END IF;

    IF LENGTH(NVL(l_rarray(i).obj_newversion_id, '')) > l_max_id_length THEN
      l_max_id_length := LENGTH(NVL(l_rarray(i).obj_newversion_id, ''));
    END IF;
  END LOOP;

  FOR i IN 1..l_rarray.COUNT LOOP
    l_temp_line := CASE
                      WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
                        l_rarray(i).obj_line_unchanged
                      WHEN l_rarray(i).obj_line_deleted IS NOT NULL THEN
                        l_rarray(i).obj_line_deleted
                      WHEN l_rarray(i).obj_line_added IS NOT NULL THEN
                        l_rarray(i).obj_line_added
                      ELSE
                        NULL
                   END;
    l_temp_line := NVL(l_temp_line, ' ');

    l_temp_head := '';
    IF l_rarray(i).obj_line_mutated = C_MUTATED THEN
      l_temp_head := l_temp_head || C_MUTATED || ':';
    END IF;
    l_temp_head :=  l_temp_head
                    || CASE
                          WHEN l_rarray(i).obj_line_deleted IS NOT NULL THEN
                            C_DELETED
                          WHEN l_rarray(i).obj_line_added IS NOT NULL THEN
                            C_ADDED
                          ELSE
                            ' '
                          END;
    l_temp_head :=  LPAD(l_temp_head, 20, ' ');

    dbms_lob.append(l_clob, l_temp_head);
    dbms_lob.append(
          l_clob
      ,
          CASE
            WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
              ' <<' || RPAD(TO_CHAR(l_rarray(i).obj_firstversion_id), l_max_id_length + 1, ' ') || '>>'
            ELSE
              ' <<' || RPAD(TO_CHAR(l_rarray(i).obj_newversion_id), l_max_id_length + 1, ' ') || '>>'
          END
       || '<<' || RPAD( NVL(TO_CHAR(l_rarray(i).obj_firstversion_line_number), ' '), LENGTH(TO_CHAR(l_rarray.COUNT)), ' ') || '>>  '
       || '<<' || RPAD( NVL(TO_CHAR(l_rarray(i).obj_newversion_line_number), ' '), LENGTH(TO_CHAR(l_rarray.COUNT)), ' ') || '>>  '
       || RPAD( NVL(REPLACE(l_temp_line, chr(10)), ' '), l_max_line_length + 10, ' ')
       || CASE
            WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
              TO_CHAR(l_rarray(i).obj_firstversion_id)
            ELSE
              '| ' || getVCObjectVersionInfo(l_rarray(i).obj_newversion_id)
          END
       || chr(10)
      );

  END LOOP;

  RETURN l_clob;
END build_allINone_clob;

/*
<comments>
  <function>
    build_allINone_clob
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes HTML table with formated versions difference report in simple text
    all-in-one style.

    [1.1] In this version it is possible to generate source code from the report
  </description>

  <parameters>
    <IN>
      [p_array]    [array with versions diffence result]    [T_ORAVC_REPORT_TABLE]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
    [1.1] [01-03-2012] [Andrey Tsyganov] [change]   [modified to be able to generate source code]
  </version>
</comments>
*/
FUNCTION build_allINone_htmlTable (
           p_array                      T_ORAVC_REPORT_TABLE
          ,p_version_id1                NUMBER
          ,p_version_id2                NUMBER
          ,p_version_generator          NUMBER := 0
          ,p_show_title                 NUMBER := 1
          ,p_show_header                NUMBER := 1
          ,p_diff_only                  NUMBER := 0
         ) RETURN CLOB IS

  l_rarray                              T_ORAVC_REPORT_TABLE;
  l_clob                                CLOB;
  l_temp_line                           CLOB := '';
  l_afterSourceGenJavaScript            VARCHAR2(4000)  :=
  '
  function generateSource() {
    var table = document.getElementById("oravc_reportable2");
    var div = document.getElementById("sourcediv");
    var checkboxes = document.getElementsByName("sourceCheckBox");
    var sourceLines = document.getElementsByName("sourceLine");

    div.innerHTML = "<table><tr><td nowrap><code>";

    for (var i = 0; i < checkboxes.length; i++) {
      // if checkbox is not checked than line is needed to new source
      if (checkboxes[i].checked == false) {
        div.innerHTML += sourceLines[i].innerHTML + "\n";
      }
    }
    div.innerHTML += "</code></td></tr></table>";
  }

  function popWindow() {
    var newwindow = window.open("","","width=600,height=600,toolbar=no,location=no,directories=no,menubar=no,scrollbars=yes,copyhistory=no,resizable=yes");
    HTMLstring ="<HTML>\n";
    HTMLstring+="<HEAD>\n";
    HTMLstring+="<TITLE>GENERATED SOURCE CODE</TITLE>\n";
    HTMLstring+="</HEAD>\n";
    HTMLstring+="<BODY>\n";
    HTMLstring+="<div>" + document.getElementById("sourcediv").innerHTML + "</div>";
    HTMLstring+="</BODY>\n";
    HTMLstring+="</HTML>";
    newwindow.document.write(HTMLstring);
    newwindow.document.close();
    //if(newwindow.focus){ newwindow.focus(); }
  }
  ';

BEGIN
  l_rarray := p_array;

  dbms_lob.createtemporary(l_clob, TRUE);
  dbms_lob.append(l_clob, '<div id="oravc_reportdiv">' || chr(10));
  dbms_lob.append(l_clob, '<table id="oravc_reportable1">' || chr(10));

  IF p_show_title = 1 THEN
    dbms_lob.append(l_clob, '<tr>' || chr(10));
    dbms_lob.append(l_clob, '<th>' || getFullVCObjectName(l_rarray(1).firstobj_id) || chr(10));
    dbms_lob.append(l_clob, '</th>' || chr(10));
    IF p_diff_only = 0 THEN
      dbms_lob.append(l_clob, '<th>Difference in versions (all-in-one only HTML report)' || chr(10));
    ELSE
      dbms_lob.append(l_clob, '<th>Difference in versions (all-in-one only changes HTML report)' || chr(10));
    END IF;
    dbms_lob.append(l_clob, '</th>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
    dbms_lob.append(l_clob, '<tr>' || chr(10));
    dbms_lob.append(l_clob, '<th>Start version</th>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || getVCObjectVersionInfo(p_version_id1) || chr(10));
    dbms_lob.append(l_clob, '</td>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
    dbms_lob.append(l_clob, '<tr>' || chr(10));
    dbms_lob.append(l_clob, '<th>End version</th>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || getVCObjectVersionInfo(p_version_id2) || chr(10));
    dbms_lob.append(l_clob, '</td>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
  END IF;
  dbms_lob.append(l_clob, '</table><br><br>' || chr(10));

  IF p_version_generator = 1 THEN
    dbms_lob.append(l_clob, '<script type="text/javascript">' || chr(10) || l_afterSourceGenJavaScript || chr(10) || '</script>'|| chr(10));
    dbms_lob.append(l_clob, '<table id="oravc_source_display">' || chr(10));
    dbms_lob.append(l_clob, '<tr>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || chr(10));
    dbms_lob.append(l_clob, '<INPUT class="oravc_button" type="button"   id="excludeAllButton" value="Exclude All" onClick="checkboxAll(''sourceCheckBox'', true);"/>'         || chr(10));
    dbms_lob.append(l_clob, '<INPUT class="oravc_button" type="button"   id="includeAllButton" value="Include All" onClick="checkboxAll(''sourceCheckBox'', false);"/>'         || chr(10));
    dbms_lob.append(l_clob, '<INPUT class="oravc_button" type="button"   id="reverseButton" value="Reverse exclude/include" onClick="checkboxReverse(''sourceCheckBox'');"/>'         || chr(10));

    dbms_lob.append(l_clob, '<INPUT class="oravc_button" type="button" name="sourceGenShowButton" value="Generate object source with the given input" onClick="generateSource();popWindow();"/>'         || chr(10));
    dbms_lob.append(l_clob, '</td>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
    dbms_lob.append(l_clob, '<tr>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || chr(10));
    dbms_lob.append(l_clob, '<div id="sourcediv" style="visibility: hidden; display: none;">test</div>'         || chr(10));
    dbms_lob.append(l_clob, '</td>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
    dbms_lob.append(l_clob, '</table>' || chr(10));
  END IF;

  dbms_lob.append(l_clob, '<table id="oravc_reportable2">' || chr(10));

  IF p_show_header = 1 THEN
    dbms_lob.append(l_clob, '<tr>'                   || chr(10));

    IF p_version_generator = 1 THEN
      dbms_lob.append(l_clob, '<th>exclude line</th>'    || chr(10));

    END IF;

    dbms_lob.append(l_clob, '<th>Version ID</th>'    || chr(10));
    dbms_lob.append(l_clob, '<th>First line</th>'    || chr(10));
    dbms_lob.append(l_clob, '<th>New line</th>'      || chr(10));
    dbms_lob.append(l_clob, '<th>Status</th>'        || chr(10));
    dbms_lob.append(l_clob, '<th>Value</th>'         || chr(10));
    dbms_lob.append(l_clob, '<th>Version info</th>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>'                  || chr(10));
  END IF;

  FOR i IN 1..l_rarray.COUNT LOOP
    IF p_diff_only = 0 OR l_rarray(i).obj_line_unchanged IS NULL THEN
      l_temp_line := CASE
                        WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
                          l_rarray(i).obj_line_unchanged
                        WHEN l_rarray(i).obj_line_deleted IS NOT NULL THEN
                          l_rarray(i).obj_line_deleted
                        WHEN l_rarray(i).obj_line_added IS NOT NULL THEN
                          l_rarray(i).obj_line_added
                        ELSE
                          NULL
                      END;

      l_temp_line := getClobForHTML(l_temp_line);

      dbms_lob.append(l_clob, '<tr '
                              ||  CASE
                                    WHEN l_rarray(i).obj_line_unchanged = C_ADDED_CHAR THEN
                                      'class="oravc_tr' || C_ADDED_CHAR2 || '"'
                                    WHEN l_rarray(i).obj_line_mutated IS NOT NULL THEN
                                      'class="oravc_tr' || C_MUTATED || '"'
                                    WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
                                      'class="oravc_tr' || C_UNCHANGED || '"'
                                    WHEN l_rarray(i).obj_line_deleted IS NOT NULL THEN
                                      'class="oravc_tr' || C_DELETED || '"'
                                    WHEN l_rarray(i).obj_line_added IS NOT NULL THEN
                                      'class="oravc_tr' || C_ADDED || '"'
                                    ELSE
                                      ''
                                    END
                              || ' >' || chr(10)
                   );

      IF p_version_generator = 1 THEN
        dbms_lob.append(l_clob, '<td>'
                              || CASE
                                   WHEN    l_rarray(i).obj_line_mutated IS NOT NULL
                                        OR l_rarray(i).obj_line_deleted IS NOT NULL
                                        OR l_rarray(i).obj_line_unchanged = C_ADDED_CHAR
                                   THEN
                                    '<input class="oravc_checkbox" name="sourceCheckBox" id="sourceCheckBox' || to_char(i) ||'" type="checkbox" checked></input>'  || chr(10)

                                   ELSE
                                    '<input class="oravc_checkbox" name="sourceCheckBox" id="sourceCheckBox' || to_char(i) ||'" type="checkbox"></input>'  || chr(10)
                                   END
                              || '</td>'
                              || chr(10)

                       );
      END IF;

      dbms_lob.append(l_clob, '<td>'
                              || CASE
                                   WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
                                    TO_CHAR(l_rarray(i).obj_firstversion_id)
                                   ELSE
                                    TO_CHAR(l_rarray(i).obj_newversion_id)
                                  END
                              || '</td>'
                              || chr(10)
                     );
      dbms_lob.append(l_clob, '<td>' || TO_CHAR(l_rarray(i).obj_firstversion_line_number) || '</td>' || chr(10));
      dbms_lob.append(l_clob, '<td>' || TO_CHAR(l_rarray(i).obj_newversion_line_number) || '</td>' || chr(10));
      dbms_lob.append(l_clob, '<td>'
                              ||  CASE
                                    WHEN l_rarray(i).obj_line_mutated = C_MUTATED THEN
                                      l_rarray(i).obj_line_mutated || ':'
                                    ELSE
                                      NULL
                                    END
                              ||  CASE
                                    WHEN l_rarray(i).obj_line_deleted IS NOT NULL THEN
                                      'DELETED'
                                    WHEN l_rarray(i).obj_line_added IS NOT NULL THEN
                                      'ADDED'
                                  ELSE
                                    ' '
                                  END
                              || '</td>'
                              || chr(10)
                     );
      dbms_lob.append(l_clob, '<td name="sourceLine" onmousedown="onSourceLineMouseAction(this,''sourceCheckBox' || to_char(i) || ''')">'
                              || l_temp_line
                              || '</td>'
                              || chr(10)
                     );
      dbms_lob.append(l_clob, '<td>'
                              || CASE
                                  WHEN l_rarray(i).obj_line_unchanged IS NOT NULL THEN
                                    TO_CHAR(l_rarray(i).obj_firstversion_id)
                                  ELSE
                                    getVCObjectVersionInfo(l_rarray(i).obj_newversion_id)
                                  END
                              || '</td>'
                              || chr(10)
                     );
    END IF;
  END LOOP;
  dbms_lob.append(l_clob, '</table>' || chr(10));
  dbms_lob.append(l_clob, '</div>' || chr(10));

  RETURN l_clob;

END build_allINone_htmlTable;


/*
<comments>
  <function>
    build_allINone_clob
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes CLOB with formated versions difference report in simple text
    side-by-side style
  </description>

  <parameters>
    <IN>
      [p_array]    [array with versions diffence result]                              [T_ORAVC_REPORT1_TABLE]
      [p_version_id1]       [version controll obeject version1 id]                    [NUMBER]
      [p_version_id2]       [version controll obeject version2 id]                    [NUMBER]
      [p_show_title]        [show title of the report: 1= true, else false]           [NUMBER]                  [1]
      [p_show_header]       [show table header for the report: 1= true, else false]   [CLOB]                    [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>
</comments>
*/
FUNCTION build_side_by_side_clob (
           p_array                      T_ORAVC_REPORT2_TABLE
         ) RETURN CLOB IS

  l_r2array                             T_ORAVC_REPORT2_TABLE;
  l_clob                                CLOB;
  l_max_line_length1                    NUMBER := -1;
  l_max_line_length2                    NUMBER := -1;
  l_temp_line                           CLOB := '';
  l_temp_head                           VARCHAR2(50) := '';
BEGIN
  l_r2array := p_array;
  dbms_lob.createtemporary(l_clob, TRUE);

  FOR i IN 1..l_r2array.COUNT LOOP
    l_temp_line := l_r2array(i).obj_firstversion_line_text;

    IF LENGTH(l_temp_line) > l_max_line_length1 THEN
      l_max_line_length1 := LENGTH(l_temp_line);
    END IF;

    l_temp_line := l_r2array(i).obj_newversion_line_text;
    IF LENGTH(l_temp_line) > l_max_line_length2 THEN
      l_max_line_length2 := LENGTH(l_temp_line);
    END IF;

  END LOOP;

  FOR i IN 1..l_r2array.COUNT LOOP

    l_temp_head := '';
    IF l_r2array(i).mutated = C_MUTATED THEN
      l_temp_head := l_temp_head || C_MUTATED || ':';
    END IF;
    l_temp_head :=  l_temp_head
                    || CASE
                          WHEN l_r2array(i).deleted IS NOT NULL THEN
                            C_DELETED
                          WHEN l_r2array(i).added IS NOT NULL THEN
                            C_ADDED
                          ELSE
                            ' '
                          END;
    l_temp_head :=  LPAD(l_temp_head, 20, ' ') || '<<';

    dbms_lob.append(l_clob, l_temp_head);

    dbms_lob.append(
          l_clob
      ,   RPAD( NVL(TO_CHAR(l_r2array(i).obj_firstversion_line_number), ' '), LENGTH(TO_CHAR(l_r2array.COUNT)), ' ') || '>>  '
       || RPAD( NVL(REPLACE(l_r2array(i).obj_firstversion_line_text, chr(10)), ' '), l_max_line_length1 + 10, ' ') || '| '
       || ' <<' || RPAD( NVL(TO_CHAR(l_r2array(i).obj_newversion_line_number), ' '), LENGTH(TO_CHAR(l_r2array.COUNT)), ' ') || '>>  '
       || RPAD( NVL(REPLACE(l_r2array(i).obj_newversion_line_text, chr(10)), ' '), l_max_line_length2 + 10, ' ')
       || CASE WHEN l_r2array(i).unchanged IS NOT NULL THEN
                 ''
               ELSE
                 '| ' || getVCObjectVersionInfo(l_r2array(i).obj_newversion_id)
          END
       || chr(10)
    );

  END LOOP;

  RETURN l_clob;
END build_side_by_side_clob;

/*
<comments>
  <function>
    build_allINone_clob
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes HTML table with formated versions difference report in simple text
    side-by-side style
  </description>

  <parameters>
    <IN>
      [p_array]    [array with versions diffence result]                              [T_ORAVC_REPORT2_TABLE]
      [p_version_id1]       [version controll obeject version1 id]                    [NUMBER]
      [p_version_id2]       [version controll obeject version2 id]                    [NUMBER]
      [p_show_title]        [show title of the report: 1= true, else false]           [NUMBER]                  [1]
      [p_show_header]       [show table header for the report: 1= true, else false]   [CLOB]                    [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-10-2011] [Andrey Tsyganov] [creation] []
  </version>
</comments>
*/
FUNCTION build_sideByside_htmlTable (
           p_array                      T_ORAVC_REPORT2_TABLE
          ,p_version_id1                NUMBER
          ,p_version_id2                NUMBER
          ,p_show_title                 NUMBER := 1
          ,p_show_header                NUMBER := 1
         ) RETURN CLOB IS

  l_r2array                             T_ORAVC_REPORT2_TABLE;
  l_clob                                CLOB;
BEGIN
  l_r2array := p_array;
  dbms_lob.createtemporary(l_clob, TRUE);
  dbms_lob.append(l_clob, '<div id="oravc_reportdiv">' || chr(10));
  dbms_lob.append(l_clob, '<table id="oravc_reportable1">' || chr(10));

  IF p_show_title = 1 THEN
    dbms_lob.append(l_clob, '<tr>'   || chr(10));
    dbms_lob.append(l_clob, '<th>' || getFullVCObjectName(l_r2array(1).firstobj_id) || chr(10));
    dbms_lob.append(l_clob, '</th>' || chr(10));
    dbms_lob.append(l_clob, '<th align=center><b>Difference in versions (side-by-side report)</b>' || chr(10));
    dbms_lob.append(l_clob, '</th>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
    dbms_lob.append(l_clob, '<tr>' || chr(10));
    dbms_lob.append(l_clob, '<th>Start version</th>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || getVCObjectVersionInfo(p_version_id1) || chr(10));
    dbms_lob.append(l_clob, '</td>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
    dbms_lob.append(l_clob, '<tr>' || chr(10));
    dbms_lob.append(l_clob, '<th>End version</th>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || getVCObjectVersionInfo(p_version_id2) || chr(10));
    dbms_lob.append(l_clob, '</td>'  || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
  END IF;

  dbms_lob.append(l_clob, '</table><br><br>' || chr(10));
  dbms_lob.append(l_clob, '<table id="oravc_reportable2">' || chr(10));

  IF p_show_header = 1 THEN
    dbms_lob.append(l_clob, '<tr>'                                 || chr(10));
    dbms_lob.append(l_clob, '<th>Line first version</th>'       || chr(10));
    dbms_lob.append(l_clob, '<th>Value first version</th>'      || chr(10));
    dbms_lob.append(l_clob, '<th>Status</th>'                   || chr(10));
    dbms_lob.append(l_clob, '<th></th>'                         || chr(10));
    dbms_lob.append(l_clob, '<th>Value last version</th>'       || chr(10));
    dbms_lob.append(l_clob, '<th>Line last version</th>'        || chr(10));
    dbms_lob.append(l_clob, '<th>Version info</th>' || chr(10));
    dbms_lob.append(l_clob, '</tr>' || chr(10));
  END IF;

  FOR i IN 1..l_r2array.COUNT LOOP
      dbms_lob.append(l_clob, '<tr '
                              ||  CASE
                                    WHEN l_r2array(i).obj_firstversion_line_text = C_ADDED_CHAR THEN
                                      'class="oravc_tr' || C_ADDED_CHAR || '"'
                                    WHEN l_r2array(i).mutated IS NOT NULL THEN
                                      'class="oravc_tr' || C_MUTATED || '"'
                                    WHEN l_r2array(i).unchanged IS NOT NULL THEN
                                      'class="oravc_tr' || C_UNCHANGED || '"'
                                    WHEN l_r2array(i).deleted IS NOT NULL THEN
                                      'class="oravc_tr' || C_DELETED || '"'
                                    WHEN l_r2array(i).added IS NOT NULL THEN
                                      'class="oravc_tr' || C_ADDED || '"'
                                    ELSE
                                      ''
                                    END
                              || ' >' || chr(10)
                   );

    dbms_lob.append(l_clob, '<td>' || TO_CHAR(l_r2array(i).obj_firstversion_line_number) || '</td>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || getClobForHTML(l_r2array(i).obj_firstversion_line_text) || '</td>' || chr(10));
    dbms_lob.append(l_clob, '<td>'
                            ||  CASE
                                  WHEN l_r2array(i).mutated = C_MUTATED THEN
                                    l_r2array(i).mutated || ':'
                                  ELSE
                                    NULL
                                END
                            ||  CASE
                                  WHEN l_r2array(i).unchanged IS NOT NULL THEN
                                    ''
                                  WHEN l_r2array(i).deleted IS NOT NULL THEN
                                    'DELETED'
                                  WHEN l_r2array(i).added IS NOT NULL THEN
                                    'ADDED'
                                  ELSE
                                    NULL
                                END
                            || '</td>'
                            || chr(10)
                   );
    dbms_lob.append(l_clob, '<td>' || '</td>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || TO_CHAR(l_r2array(i).obj_newversion_line_number) || '</td>' || chr(10));
    dbms_lob.append(l_clob, '<td>' || getClobForHTML(l_r2array(i).obj_newversion_line_text) || '</td>' || chr(10));
    dbms_lob.append(l_clob, '<td>'
                            ||  CASE WHEN l_r2array(i).unchanged IS NOT NULL THEN
                                  NULL
                                ELSE
                                  getVCObjectVersionInfo(l_r2array(i).obj_newversion_id)
                                END
                            || '</td>'
                            || chr(10)
                   );
  END LOOP;

  dbms_lob.append(l_clob, '</table>' || chr(10));
  dbms_lob.append(l_clob, '</div>'   || chr(10));

  RETURN l_clob;
END build_sideByside_htmlTable;


/*
<comments>
  in package specification

</comments>
*/
FUNCTION allINone_diff_clob (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN CLOB
  IS
  l_rarray                              T_ORAVC_REPORT_TABLE;
  l_result_code                         NUMBER;
BEGIN
  l_rarray := oravc_allINone_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob, l_result_code);

  RETURN build_allINone_clob(l_rarray);
END allINone_diff_clob;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION sideBYside_diff_clob (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN CLOB
  IS
  l_r2array                             T_ORAVC_REPORT2_TABLE;
BEGIN
  l_r2array := oravc_sideBYside_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob);

  RETURN build_side_by_side_clob(l_r2array);
END sideBYside_diff_clob;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION allINone_diff_htmlTable (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_diff_only                 NUMBER := 0
           ,p_version_generator         NUMBER := 0
           ,p_show_title                NUMBER := 1
           ,p_show_header               NUMBER := 1
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN CLOB
  IS
  l_rarray                              T_ORAVC_REPORT_TABLE;
  l_clob                                CLOB;
  l_result_code                         NUMBER;

BEGIN
  l_rarray := oravc_allINone_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob, l_result_code);

  RETURN build_allINone_htmlTable(
           p_array             => l_rarray
          ,p_version_generator => p_version_generator
          ,p_diff_only         => p_diff_only
          ,p_version_id1       => p_version_id1
          ,p_version_id2       => p_version_id2
          ,p_show_title        => p_show_title
          ,p_show_header       => p_show_header
        );
END allINone_diff_htmlTable;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION sideBYside_diff_htmlTable (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_show_title                NUMBER := 1
           ,p_show_header               NUMBER := 1
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) RETURN CLOB
  IS
  l_r2array                             T_ORAVC_REPORT2_TABLE;
  l_clob                                CLOB;

BEGIN
  l_r2array := oravc_sideBYside_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob);

  RETURN build_sideByside_htmlTable(
           p_array      => l_r2array
          ,p_version_id1 => p_version_id1
          ,p_version_id2 => p_version_id2
          ,p_show_title  => p_show_title
          ,p_show_header => p_show_header
        );
END sideBYside_diff_htmlTable;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE allINone_diff_totable (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_rarray                              T_ORAVC_REPORT_TABLE;
  l_session_id                          NUMBER;
  l_result_code                         NUMBER;

BEGIN
  l_rarray     := oravc_allINone_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob, l_result_code);
  l_session_id := userenv('sessionid');

  DELETE FROM oravc_report1
        WHERE session_id = l_session_id;

  FOR i IN 1..l_rarray.COUNT LOOP
    INSERT INTO oravc_report1(
       session_id
      ,insert_date
      ,line_order
      ,firstobj_id
      ,obj_firstversion_id
      ,obj_firstversion_line_number
      ,obj_firstver_new_line_number
      ,obj_line_unchanged
      ,obj_line_deleted
      ,obj_line_added
      ,obj_line_mutated
      ,obj_newversion_id
      ,obj_newversion_line_number
      )
   VALUES (
       l_session_id
      ,SYSDATE
      ,l_rarray(i).line_order
      ,l_rarray(i).firstobj_id
      ,l_rarray(i).obj_firstversion_id
      ,l_rarray(i).obj_firstversion_line_number
      ,l_rarray(i).obj_firstver_new_line_number
      ,l_rarray(i).obj_line_unchanged
      ,l_rarray(i).obj_line_deleted
      ,l_rarray(i).obj_line_added
      ,l_rarray(i).obj_line_mutated
      ,l_rarray(i).obj_newversion_id
      ,l_rarray(i).obj_newversion_line_number
    );

  END LOOP;

  COMMIT;
END allINone_diff_totable;

/*
<comments>
  in package specification

</comments>
*/
PROCEDURE sideBYside_diff_totable (
            p_version_id1               NUMBER
           ,p_version_id2               NUMBER
           ,p_forceCompare              NUMBER := 0
           ,p_version1_clob             CLOB   := NULL
           ,p_version2_clob             CLOB   := NULL
         ) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_r2array                             T_ORAVC_REPORT2_TABLE;
  l_session_id                          NUMBER;

BEGIN
  l_r2array    := oravc_sideBYside_diffA(p_version_id1, p_version_id2, p_forceCompare, p_version1_clob, p_version2_clob);
  l_session_id := userenv('sessionid');

  DELETE FROM oravc_report2
        WHERE session_id = l_session_id;

  FOR i IN 1..l_r2array.COUNT LOOP
    INSERT INTO oravc_report2(
       session_id
      ,insert_date
      ,line_order
      ,firstobj_id
      ,obj_firstversion_id
      ,newobj_id
      ,obj_newversion_id
      ,unchanged
      ,deleted
      ,added
      ,mutated
      ,obj_firstversion_line_number
      ,obj_firstver_new_line_number
      ,obj_firstversion_line_text
      ,obj_newversion_line_number
      ,obj_newversion_line_text
      )
   VALUES (
       l_session_id
      ,SYSDATE
      ,l_r2array(i).line_order
      ,l_r2array(i).firstobj_id
      ,l_r2array(i).obj_firstversion_id
      ,l_r2array(i).newobj_id
      ,l_r2array(i).obj_newversion_id
      ,l_r2array(i).unchanged
      ,l_r2array(i).deleted
      ,l_r2array(i).added
      ,l_r2array(i).mutated
      ,l_r2array(i).obj_firstversion_line_number
      ,l_r2array(i).obj_firstver_new_line_number
      ,l_r2array(i).obj_firstversion_line_text
      ,l_r2array(i).obj_newversion_line_number
      ,l_r2array(i).obj_newversion_line_text
    );

  END LOOP;

  COMMIT;
END sideBYside_diff_totable;

END ORAVC_ENGINE;
/
